Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data.
However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Highlighting A Row And Having That Data Tranfered To Another Worksheet
I have 1 Excel Doc that has 3 worksheets (DATA, COVER, TPA). DATA is where i input the data and COVER and TPA are where the data needs to end up. both COVER and TPA are standardized sheets that i do not change...only the data coming in will change. How can i make it so that whatever row in DATA i highlight, that row's data will be entered in to the correct spots on both COVER and TPA. when i highlight a different row, then the data will change to that row's data in both COVER and TPA (hope i'm explaining this correctly). right now i can only do 1 row by using (=DATA!$D$2). basically i want the '2' that is representing the row to be whatever row i have highlighted. if i highlight row 51 i want the formula to read (=DATA!$D$51). can anyone please help. i can put up a copy of the spreadsheet if need be, i am just hesitant cause of the confidential info. i would need to change the names and numbers to protect the innocent.
View Replies!
View Related
Highlighting The Row With The Selected Cell
I work with many sheets that have many data columns and have used a "greenbar" formula in conditional formating but would like to have a similar formula that highlights the current row. If possible, it would be nice if this new formula would not wipe out all other formatting as it moves. I have seen Ivan's work at www.mrexcel.com/hof001.php but it says that it wipes out other formatting. The formula that I am currently using is =MOD(ROW(),3)=2 Is there a way of getting the current row number so that it could be =ROW()=(current row number) ?
View Replies!
View Related
VBA Code To Select Last Cell In Row With Data
I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work.
View Replies!
View Related
Transpose The Data To Have Only One Item Per Row
When I download pricing from SAP, the output vertically displays material, scale quantity, price (three columns of data). There could be up to nine price breaks per item and there could be as few a one. How could I transpose the data to have only one item per row followed by the scale quantities and prices in the proceeding 18 columns. Keep in mind the the quantities very greatly between items. A value of zero should be displayed when the full 9 quantity and price breaks are not used.
View Replies!
View Related
Pivot Table, Calculated Item.... No Data, Still Creates A Row
I have a Pivot table. Layout: Rows: City, Restaurant, Cashflow Column: Date (Month/Year) DataField: $Amount "City" might be 7 cities "Restaurant" may be 32 restaurants, some in some cities; but not other "CashFlow" can be either "Revenue" or "Expense" The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist. Now.... the trouble... I added a Calculated Item, "Profit" which = "Revenue" - "Expenses". Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries. How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination? Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?
View Replies!
View Related
Update Whole Entry, If One Item Change From List
Suppose I have a department List, and I made a list from that column (Department List), and I enter more staff and selected a department for them during registering, now one of the Department List Item change, How is it possible to change all the entry which has that department? For more info see the attachment.
View Replies!
View Related
Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error. Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used .Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0 after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View Replies!
View Related
Select Item In Listbox Via Macro
How do I select the first item in a listbox via code (userform). Scenerio: A user starts with listbox3 and makes a selection for a font type. If the font selected doesnt exist on there system (code already taken care of, true or false) I want to automatically set the font to the first item in listbox3 as a default value transparently so the user can continue.
View Replies!
View Related
Macro To Select A Menu Item
I am wondering if there is any code to SELECT (DESELECT) A MENU ITEM using VBA? I have an Excel Add-in program installed, which adds a Menu "Easycal" to my Excel menu bar. Under "Easycal" (drop down list), I have the following items: Refresh workbook Refresh worksheet Turnoff Easycal I am using "Easycal"'s functions in various cells of my sheet "Final". When I select "Refresh worksheet" from "Final", "Easycal" fetches (or calculates) me the data from the "Easycal" database. I have a macro (on "Final") that pulls together data from other sheets of the same workbook. I there anyway to include a code in my existing macro, so that when I run the macro, the following are completed in sequence: 1. Select "Turnoff Easycal" menu item 2. Run my existing macro (to pull the data from other sheets to "Final") 3. DeSelect "Turnoff Easycal" menu item 4. Select "Refresh worksheet" menu item
View Replies!
View Related
Select Listbox Item On Key Press
I'm using list boxes in an excel sheet. What I want to do, if at all possible, is to be able to TAB to the list box and then hit the first letter of the City I'm trying to go to. The list is in alphabetical order, but if I tab to it and want Philly, I want to just be able to hit the P button and get to the Ps.
View Replies!
View Related
Select Item In Range Chosen From ListBox
I have a list of country names in a listbox that are populated using another worksheet that has these country names. When I make a selection in the listbox, I want the same country names to be highlighted in the worksheet. I will then be able to run a macro that takes those selected cells as input.
View Replies!
View Related
Force User To Select A Listbox Item
I am attempting to use an if statement to stop a user from trying to input a listbox as a null value. The if then statement is not working. The listbox value is null but the if statement doesn't react. If listbox1.Value = Null Then MsgBox "you must select a serial number" Exit Sub End If
View Replies!
View Related
Select/Highlight Item X Of Listbox On Worksheet
I have a ListBox that comes from the Control ToolBox I know how how fix ListBox1.ListIndex = 0 my problem comes because when i run a Macro... I lost the focus of my listbox, I mean once I move around the workbook through vba the listbox does not show anymore... one item of the listbox selected, I mean in blue. Is there a way to do kind of: activesheet.ListBox1.Selected(5) = True I I want to see the fifth item of my Conbo Box on blue, I mean selected
View Replies!
View Related
Select First Data In A Row
I want to display the first value in a row in a specific column of cells. Example: Row 5 contains data in columns E thru L Row 6 contains data in columns G & H Row 7 has data in only column I I want to display only the first value in each row in column B according to row. if this is not clear enough please let me know and I will try to clarify.
View Replies!
View Related
Select Nth Item From Validation List Macro
I have a normal data validation box on excel, but I want to be able to navigate my way through it using a macro. I can select & opent the drop down list with the following SendKeys "%{DOWN}" but then an action like SendKeys "{UP}" Does not navigate itself up the menu as a normal keystoke would. Any ideas on how to solve this? Basically, I want to select a cell, open the drop down menu, select the first choice in it (so thinking SendKeys "{HOME}{ENTER}" ) all using a macro.
View Replies!
View Related
Automatically Update Cell From New Row Entries
I have a worksheet(A) with rows of data associated to a date that gets a new row of data points once a month. On the second worksheet(B) I have two columns, one (Current Month) to show the data in the newest row (transposed), and one (Previous Month) to show the data in the row above it (from the previous month)(also transposed). Is there any way to get the columns in worksheet B to automatically read the newest row of data from worksheet A into column Current Month and automatically update the column Previous Month with the row above it?
View Replies!
View Related
Change Row Based On Cell Update
I`m trying to work out the best way to propogate a cell's format accross an entire range. What I ultimately want is for a user to only have to change the text colour of one cell, and for the entire row to immediately change to the same colour with it. Even if this means always having to update for example the cell in column A to change the text colour accross that row.
View Replies!
View Related
Highlighting Entire Row
This problem has been driving me insane, as I thought it wasn't possible to do in excel however this morning I accidentally pressed a shortcut key on my keyboard and what I wanted, happened.. pity I can't remember how I did it. How do I highlight an entire row, but remains highlighted when i enter info in the cells until i move down to the next cell? Eg. I click on row"1" so that all cells are highlighted, then while entering info in B:1 - the entire row is still highlighted? This will be very useful in my line of work. I know there was some information on conditioning formatting, but surely there is a way to do it via keyboard shortcut since i accidentally did it just before.
View Replies!
View Related
Macro To Select Next Row Of Data And Save As
I am pretty much a novice at this whole excel / vba stuff, but I have been trying! unfortunately I have now got well and truely stumped! I require the macro to perform a loop and once saved the first row of data, select the next row of data (in exactly the same way) until it reaches the last row. The macro I have created does the first part fine, but I have reached the end of my knowledge base! Below is the macro I have created: Sub TEST_1() ' ' TEST_1 Macro ' Macro recorded 20/08/2009 by ..........' ' Sheets("Input").Select Range("A1:CI1").Select Selection.Copy Sheets("Results").Select Range("G3").Select ActiveWindow.LargeScroll ToRight:=7 Range("G3:CO3").Select..............
View Replies!
View Related
Stacked Column Chart (tying Up The Loose Ends)
If the user adds 3 new lines, to the data table, – the Series Value Ranges should increase by 3 lines – AUTOMATICALLY(!) to avoid the user to full-around with the chart. It should take care of the 4 Series Ranges and also of the Range for the X-Axis Category Labels. As far as I recall, the way to accomplish it, is by NAMING the ranges with COUNTA etcc BUT, here – the last row is an empty rowc
View Replies!
View Related
Select An Item From Drop Down List And Automatically Fill A Number Of Cells
I am kind of new to Excel programming and would like to know if there is a solution to my problem. What I need is to have a dropdown list (I know how to make this) then select an item from the drop down, then based on the name i select, Excel automatically fill up the rest of the cells. For example, from the drop down list I select the company name then Excel automatically type the address, phone number, Fax number and the contact name of the company for me in corresponding cells. Is there a way to do this?
View Replies!
View Related
Select Column And Delete Row Where Data Is Duplicate
I've got this macro which works well, but I have to edit it to change the column I want to check for dupes. I don't mind doing that, but now I have to share the macro with someone who is not comfortable changing the references in the code every time they run it. As it is it checks column G:G (7) for dupes and deletes the row. Good. I'd like for it to do the same thing, but for whatever column the active cell is in. I can use this: Col = ActiveCell.Column in place of the "7" in the With Range part but I don't know how to replace the "G1:G". Sub DeleteDuplicatesColumnG() With Application .ScreenUpdating = False .EnableEvents = False
View Replies!
View Related
VBA Code To Lookup Value In Range & Update Cell In Row
I have a function that is called upon saving a UserForm. One of the Text Labels contains the ID field that I need to use to do a lookup into the first column of a Named Range and then I need to update the cell in the 4th column of that row. From my research, I'm assuming it will go something like this: Application.WorksheetFunction.VLookup(valve, Worksheets("TestWorksheet").Range("TestRange".Cells), 10, False).Value = "TestValue"
View Replies!
View Related
Select Row Where Only 1 Cell Is Selected
I need to delete part of the row where one cell is selected. If more than 1 cell is selected, I want a msgbox. Example: I have any cell selected on row 26. I want to select all cells in this row including column C thru column AD. Column A & B are not to be deleted. After the selection is made, I want the row deleted and the below cells moved up.
View Replies!
View Related
Select The Last Non-null Cell In A Row Or Column
I am finding that I am using the 'go to' option frequently in my macros, but I feel very limited by the options that I have (I need to learn VB-I know) I would like primarily to discover how to select the last cell containing anything (not including blanks like the 'last cell' option in the edit>go to) within a selected row. Ideally, I would like a formula to fill downward a given amount of rows so as to prevent myself from having to fill them all the way down to the end of the spreadsheet (therebye making the file to large). Also, If anyone could guide me to a resource at which I can find similar commands.
View Replies!
View Related
Macro To Select Cell After Last Row In Column
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case) (I know I can record a macro but the number of rows can potentially differ every time I run the macro) N.B. Excel version 2003
View Replies!
View Related
Macro: Select Cell A Of Selected Row
Currently working on a project for university and still learning how to use macro. Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc.. There are many rows with data in them. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Find Next Empty Row And Select Cell In Column A
how i can go about finding the next empty row (and select the first cell of that row (column A))? edit: It probably should be noted that there are cells in Columns A through P. There are rows where all and/or just one cell contains data per row. So i cannot use a " lookup" based on a single column.
View Replies!
View Related
Lookup Data In Row & Cell Based On Data In Row And Cell
I am looking for a way to do what i would call a dual vlookup. i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same. what i am trying to do is something like vlookup("a",sheet2!A:M,***need to put the column of the data here***,0) i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
View Replies!
View Related
Highlighting Data
I have a spreadsheet that I need to highlight the last row next to the name in column E where the date in column C is 35 days or less than the current date in cell A2. For eg if there are 10 names containing the name "James" in Column E and the last date in column C (say C20) in line with James is 35 days or less than the current date in A2, then the entire row from A to G for eg A20:F20 must be highlighted in grey. I need VBA code that will do this for me. I have attached in sample spreadshhet. In the attached example A14:G14 and A16:G16 must be highlighted in grey
View Replies!
View Related
Match An Item In A Row With Another Row?
how I can match an item in a row with another row? I have a row of data A2:J2. A persons name could be in any column in that row. I have another two rows of data where the peoples name and department name appear Where L2:L7 = peoples names where M2:M7 = peoples department I want to match a name in A2:J2 with one in L2:L7 and if matched put the department name in K2. Then obiviously i will copy the formular down column K.
View Replies!
View Related
|
TRACKER
