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 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
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 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
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
Change Color Of Row When Cell Is Selected
I have a spreadsheet containing many rows of data that I need people to review. After reviewing the data in the column, a reviewer must enter comments in the right-most column and then hit the Enter key to move to the next row down. What I'd like to happen -- As a reviewer selects the cell where they need to enter data, I want the entire row to highlight. I can use conditional formatting to change the color of the row once data is entered, but I want to change the color of the row when a specific cell is selected. I've attached a sample spreadsheet.
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
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
Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives. After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero). Listbox and expected result are in attached example worksheet. The attached does show the need much better then I can explain it.
View Replies!
View Related
Elseif Statement In Vba: If The Selected Cell Falls Between 1/01/06 And 31/01/06 Then Jan Would Be Selected
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months. Sub test() Dim SelectDate As Range Set SelectDate = Range("SelectedDate") If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Jan" ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Feb"........................
View Replies!
View Related
Insert Range-named Row At User-selected Row
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet. If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position). This is what I have tried so far:
View Replies!
View Related
Highlighting The Active Cell After A Highlight
I have a cover sheet to a workbook that holds hyperlinks to its relevent page. Several of the hyperlinks link to the same page but different relevant cells. However it is not overly clear which cell is active when you click the hyperlink. What I would like is for the active cell to be highlighted on the page. EG hyperlink 1 links to page 1 cell A2 & hyperlink 2 links to page 1 cell B6 - I would like A2 to be highlighted when accessed by hyperlink 1 & B6 highlighted when accessed by hyperlink 2.
View Replies!
View Related
Highlighting A Cell, Based On Value / Blank.
I have a macro, and it mainly works, except the second part where it checks if the value is less than 7, and returns it to a white background. The cell has a formula in it that returns "" based on another series of cells. I have considered changing that to a zero and formating, but for reasons that don't need explanation, i have chosen not to. here is the macro. I need the second part to be activated if less than 7, as well as if the cell returns ""
View Replies!
View Related
Highlighting Cells Depending On A Value In Another Cell
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), that’s all I’m interested in ( multiple locations). What I’ve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 50). The warehouse runs up one aisle and then down the next and so on. So I need to be able to highlight cells up one column and then down the next and then up, then down and so on. Multiple locations change on a daily bases, so need a quick way of updating 4000 locations. New to excel don’t know if this is possible or not. Learning as I go. Need some assistance. Sample below. Only interested if a number is returned, “******” can be ignored as will hide all columns expect ones with locations. So: BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049. BB780 returned value of 2, so would like to highlight BB780, BB789. POSSIBLE? BA090 2 BA100 **** BB749 **** BB739 **** BA079 **** BA089 **** BB760 **** BB750 **** BA070 **** BA080 **** BB769 **** BB759 **** BA059 **** BA069 **** BB780 2 BB770 **** BA050 **** BA060 4 BB789 **** BB779 **** BA039 **** BA049 **** BB800 **** BB790 **** BA030 4 BA040 **** BB809 **** BB799 **** BA019 **** BA029 **** BB820 **** BB810 **** BA010 **** BA020 4 BB829 **** BB819 ****
View Replies!
View Related
Highlight A Selected Row
just bought a 22" wide screen monitor and when working in excel it's difficult to follow from left to right in a row i am looking at. i have 22 columns of info that are in view at any given time. the row number on the left gets highlighted and the cell i happen to be in is outlined with a double line border. is there a way to get the whole row outlined like the single cell? i know i can achieve this by clicking on the row itself, but i tend to move a lot around the spreadsheet via the arrow keys.
View Replies!
View Related
Deleting A Selected Row
I would like to run a macro that will delete the selected row in a protected worksheet but only delete it if the row does not have locked cells. I want users to be able to delete the rows they no longer need but only be able to delete them if the row does not have formulas that are locked. BTW-I am working in an older version of Excel.
View Replies!
View Related
How To Goto First Column Of Selected Row
i know the keyboard shortcut for doing this is End + arrow key... but wat if there are value in between for example Cat..""..""..""..""..""..""..""..5.."".."" hitting the End + arrow key from the very end will move selection to value 5... there are empty spaces in between.. 1. is there a way to convert the blank spaces in excel to 0 as default value. 2. is there any other keyboard shortcut key to reach to Cat without inturrupting 5 in between..
View Replies!
View Related
Check Whether Entire Row Has Been Selected
I have a button on an excel sheet call 'Delete Row' where I want to put code underneath so that a user can delete an enirerow. I have the Selection.EntireRow.Delete Shift:=xlUp at the moment but I want to check that the user has selected the entirerow before I delete it.
View Replies!
View Related
Populate Selected Row From Form
Thought it was better to start a new thread than to continue the old one since the focus in that thread was to get the values into a form and the question this tme is to get the values back into the correct row. Background. My little application does the following... In Form1 the user adds information that is inserted as a new row in my excel sheet. A unique ID is created and inserted in column A. In Form2 the user sees a listbox with the rows in the excel sheet. The user then selects one of the rows and get all the information including the ID displayed to him/her in Form3. In Form3 the user should then be able to update the values (except the ID) and then the values should be inserted back into the excel sheet in the correct row. I suppose I need the code behind my OKbutton to say something like If Column A = value of IDBox, insert values in columnB, columnC etc... But how do I write this code correctly?
View Replies!
View Related
Auto Filter On Selected Row
After posting a thread regarding sorting issues with multiple columns (under the thread heading "Bulk Sorting" I have conceded that unless I have a sort filter on every column I will not be able to keep data in respective rows when sorting. My new plan of attack is to insert an empty row (it will be row 13) and apply auto filter across every column (not what I wanted visually but no other option). Is there a way that I can do this at row 13 on an almost completed sheet? I may just be dumb but I can't stop the filter applying itself to row 1. If I select the whole of row 13 the auto filter or "right click - apply filter" options aren't available. If not I could start a fresh sheet. Is there a way to copy and paste a large amount of cells with different width columns into a new worksheet?
View Replies!
View Related
Delete Selected Row, And All Rows Below **
I have the following code, to find the 1st cell in column CG that contains a value of more than -200. I wish to delete the entire row that this resides on, and all rows below. Range("CG2").Select Do While Not Selection.Value > -200 Selection.Offset(1, 0).Select Loop
View Replies!
View Related
Move Selected Row To Another Worksheet
I have a workbook with multiple sheets. I need to create a macro and associate it with a command button that will cut the row the user has selected and paste it into the last empty row on another sheet in the same workbook, then, go back to the first sheet and delete the empty row.
View Replies!
View Related
Copying Selected Data In A Row To Other Sheet
I have a workbook with two sheets. The first sheet called "Master List" is a detailed product listing sheet for our use in the company that I would like to set up a script to copy only a few of the fields in the row into the next sheet. The second sheet is the "Estimate" sheet that then gets put together for the customer. Since our estimator is not much of an Excel guy I am trying to make his life easier here. I created a column (A) in front of all the other data in the rows. If he puts an X in that field, then that row needs to selectively move to the other sheet. These estimates can have 50 rows so it takes him a long time to cut and paste. If my idea is not the best way to execute this, I'm all ears to the wisdom found here. I'm using the latest version of excel on Vista Ultimate.
View Replies!
View Related
Copy Cells In Selected Row To New Sheet
I would like some code to allow the user to select any row in Sheet1 and then the populated cells (10 in every case) in that row will copy across to various locations in Sheet2. eg, The user selects Row header for Row 12 which has 10 cells ( A12 to J12) populated. A12 will go to C3 on Sheet 2, B12 to D5, C12 to D9, etc, etc.
View Replies!
View Related
Delete Row When A Selected Word Is In A Sentence
i have this macro that currently looks in column J and if a cell contains "Total" it deletes the entire row. Now what i need is to check in same column but i need it to delete the entire row if any part of the sentence in a cell within column J contains "Main Total" Sub Macro1() 'this deletes all rows if cell in column J says "total" Dim DeleteValue2 As String Dim rng2 As Range Dim calcmode2 As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With
View Replies!
View Related
Refer To The Last Row In A Selected Area Using VBA..?
I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So... If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?
View Replies!
View Related
SUMIF Function Based On Row Selected
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in. background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
View Replies!
View Related
Repeat Insert Row And Formula For Selected Cells
I am in the process of developing a budget template for next year and here is what I want to accomplish through Macro to avoid the tedious mannual manipulation process (hope that's achievable). BTW, I am only an entry-level Macro user who has no background in coding. I have attached an Excel sample but let me explain: In each of the expenses tabs (1, 2, and so on (only 1, 2 given in the sample)), I want to be able to select a range of the data cells (A19:Q34 in "Expense 1" and A10:Q28 in "Expense 2") and run this Macro so that: 1. 10 blank rows be inserted between selected data rows 2. A sum created for each month from the four rows below) with different color 3. A Year to Date Actual and To Year End Forecast created based on the VLOOKUP of the "P&L" matching the account code by month 4. Finally grouping of the first 9 rows. the above is what I have manually created for the first account code in the "expense 1" tab. I have 7-8 expenses groups and about 200 account codes. So mannually creating the above for each expense groups is not that terrible if this will make life easier for Macro. I would think this is a very typical Macro issue but can't really come up with anything. The other issue I ran into the VLOOKUP for the YTD Actual and TYE Forecast. It is to look up accont code in the "P&L" tab and match the #s. But the column numbers in the VLOOKUP do not change automatically when I copy across. Thus I have to change mannually, which is really a pain in the butt. I also know I can't copy down because that will change the account code that I want it to match. Because to be able to copy the VLOOKUP formula across the same account code, I need to use the $ to fix the account code. But to copy down I think I need that to be without the $ sign. Any solution on that?
View Replies!
View Related
Macro To Concat Selected Rows Into One Row W/no Dupes
I would like a macro to invoke after selecting multiple rows that would be concatenated into a single row containing no dupes within each column of selected cells. Details: I would like to select '2 to n' rows. I would like the macro to concatenate the cells of the columns only within the selected range. However, if any of those cells are duplicates or blanks, I don't want them included in the concatenated cell. After the concatenation of all selected rows is complete, I'd like the macro to delete all the selected rows, except for the final concatenated row. [NOTE: Rows 3 and 4 (below) would have been deleted by the macro. Row 8 would become the final product in row 2's place.] ******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1= ABCDEF1Sample*Data:*****2aaaxxxflintstone,*fredXYthese*are*notes3aaaxxyrubble,*barney*N*4aaaxyz*X*add*notes5******6******7Desired*Output:*****8aaaxxx xxy xyzflintstone,*fred rubble,*barneyXY Nthese*are*notes add*notesSheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Macro When Selected Will Take Me To A Specific Row And Column In Another Worksheet
I want to create a macro that when selected will take me to a specific row and column in another worksheet, this will be the outcome of two cells, so far i have it to take me to the row but no reference to the column can anyone help. code i have so far. Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString = Sheets("Dynamic Whereabouts").Range("B1:B1") With Sheets("whereabouts 08").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _
View Replies!
View Related
Populate Separate UserForm From Row Of Selected Listbox Item
How do I populate a new userform from an entry selected in the listbox. The listbox only lists one item from the original table but I'd like to populate the new userform with related information from the same row. I'd then like to save this information to a new sheet and store the original information on another sheet leaving the first sheet a table of information yet to be updated. The attached workbook should make things clearer. The update button is the one in question.
View Replies!
View Related
Interval Distribution Plot Chart/Graph From Selected Row
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below: ... C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ... E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ... G9 111568 298021 12940 31645 181797 36996 ... .... ... I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked? Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly.
View Replies!
View Related
Plot Each Selected Row Range As Series In Scatter Chart
I need to plot multiple series in a scatterplot. The problem is that the number of series is user-defined. And also, the number of data poitns in each series is different. I've looked it up already, but a lot of the examples i've seen don't account for the fact that I need to run through a loop and add series into my graph. In other words, i need a way to select a certain range each time it runs through the loop, and add the data as a series into the chart. Also, the user enters how many series there are, so I use that as a counter
View Replies!
View Related
Command Button: Copy Row Selected On Sheet2 Into Cells On Sheet 1
figuring out a code for a command button. I have 2 sheets open with the following set up: Sheet1 A2 = Name (chosen off sheet2) A3 = Job Title (chosen off sheet2) A4 = Department (chosen off sheet2) Sheet2 Column A = List of names Column B = Corresponding Job Title Column C = Corresponding Department I need to be able to choose a name off sheet2, click the command button and it send selected name, job title, and department to sheet1 to the respective cells.
View Replies!
View Related
|
TRACKER
