|
Highlight the Row of the Selected Cell
This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid background color and remove the color when a different cell is selected within the excel spreadsheet. Anytime you select a different cell, the new column and row will be highlighted. This is a great macro for zeroing in on a specific cell during a presentation or just when working in a really big spreadsheet. Also, if you remove the gridlines from the spreadsheet, it can be hard to determine which row or column a cell is in and this will allow you to figure that out much quicker.
This macro will overwrite any previous color in the selected cell and row. That means that if you have a worksheet with a lot of background colors and you use this macro and start selecting cells, the previous background colors will disappear.
To change the color of the highlight change the number in this line of code .ColorIndex = 6.
Where to install the macro: Worksheet
Highlight the Row of the Selected Cell
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
If rr <> "" Then
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If
r = Selection.Row
rr = r
With Rows(r).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
I want to try and set a system where by when a user selects a cell in column S that, column B shows in yellow. ie If I select S20 then B20 shows in yellow. Ideally it would be nice that B only shows highlighted if there is text in it but if thats more complicated then it doesn't matter.
Does anyone have any ideas please?
I have a formula to sellect the lowest value in a row of cells =MIN(A1,A2,A3,A4,A5,A6....) but i would like that lowest cell to become highlighted when selected so i can see a glace which number is being used. Any help would be much appriceated. jk
I have a code here that will highlight the entire row when one of the cell is selected.
Any one can modify this to just the first cell of the selected row? Thanks a lot!
Here's the code:
VB:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
What I would like to do is, if I select cell B7, is to highlight B7:K7, or if I selected B24 then it would highlight B24:K24.
Would someone be able to guide me on this one please
Many thanks
Paul
Hello, I have a worksheet with lots of data on it, resulting in a very busy layout. I have been using "FIND" to search for various numbers within the spreadsheet, however often times it is hard to see which cell "FIND" selects.
Is there a way to enable Excel to highlight the selected cell? For example could "Find" turn the cell yellow when it finds a match?
Thanks in advance for any suggestions
In the attached sheet, i am trying to highlight the cells based on the resource
for ex
A2..An defines skills
C1,D1,E1... are weeks
C2 till G4 are all resources available for that week for that skill set. (these values can extend..)
in A6 i have projects defined and resource assigned for that particular week.
My question is that When i place my cursor on C6 i need to highlight values wherver That value exists under skills
in this case Resource name is AP, so it should highlight C2,E4.
How can i achieve this , or any other way , all i need is somekind of indication that to know wherever those resources are booked and available
Thanks in advance
Hi,
I want to know if there is any method to highlight the Full Row and full column of the selected cell of the work sheet.
For example, suppose the cell is in C3. I need both Column C and Row 3 to be highlighted or to be filled with any color that I choose (Just think Yellow). Is this possible?
Dear All
Whatever cell I select, I want to hightlight the relative row and column of
that cell, how?
Thanks in advance.
Hi,
I've found examples of how to highlight the whole row of where the cursor currently is (e.g. http://www.mrexcel.com/archive2/9300/10447.htm), but I wish to only apply the highlighting to cells A and B.
Would someone be able to show me how to only highlight these (in say, yellow), and return them to white when the cursor is not in their row? The other cells have formatting which I do not want to change.
Thanks
Is there a way to temporarily highlight multiple cells when one cell is clicked on (active)?
Example: Click on A1 and then B1, C2, D7, E10 all become highlighted automatically.
|
|
