Highlight Selected Cell or Row in Excel - Aus Tom Urtis

Back to list

It's literally this easy, if you know how.

To highlight the selected cell (automatically that is.. not doing something each time you select something :) :

Get to the Visual Basic editor (might have to enable the Developer tab and then hit View Code -- google this for the way, it's not here)
Then, paste (thanks TU) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False ' Clear the color of all the cells Cells.Interior.ColorIndex = 0 ' Highlight the active cell Target.Interior.ColorIndex = 8 Application.ScreenUpdating = True End Sub

That's it. You just paste this and File > Close and Return to Excel and MAGIC happens! Sufficiently advanced technology!!

And for highlighting the entire row AND column (tweak the code as necessary if you want only..) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' Clear the color of all the cells Cells.Interior.ColorIndex = 0 With Target ' Highlight the entire row and column that contain the active cell .EntireRow.Interior.ColorIndex = 8 .EntireColumn.Interior.ColorIndex = 8 End With Application.ScreenUpdating = True End Sub

And how do you know what number to use for the ColorIndex?

http://dmcritchie.mvps.org/excel/colors.htm

If you want his book :