How to highlight active row and column?

[rjamison]

I have a very large table and I am hoping to find a way to highligh the
current row and coloum somehow to allow a crosshair like look that follows
the active cell. The table is a look up table and following the lines can
be
a pain even with grided shading.

 

[Guest]

right click on your sheet tab and paste this into the code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TheRange As Range

Set TheRange = Union(Target.EntireColumn, Target.EntireRow, Target)

Application.EnableEvents = False
TheRange.Select
Target.Activate
Application.EnableEvents = True

End Sub

 

[Guest]

when you said highlight, I assumed you meant to select the row and column,
but realized this would be very bad as you would never be able to
delete/change an individual cell. also, the previous macro would not work if
you entered a value and hit enter.

I'd suggest using two macros in the worksheet module and changing the fill
color. these macros use grey to shade the activecell row and column, but you
can use whatever color you like. just turn on the macro recorder, select a
fill color, turn off recorder and go look at the code that was written to
determine what the colorindex is.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TheRange As Range

Set TheRange = Union(Target.EntireColumn, Target.EntireRow)

Application.EnableEvents = False
Cells.Interior.ColorIndex = xlNone
TheRange.Interior.ColorIndex = 15
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TheRange As Range

Set TheRange = Union(Target.EntireColumn, Target.EntireRow)

Application.EnableEvents = False
Cells.Interior.ColorIndex = xlNone
TheRange.Interior.ColorIndex = 15
Application.EnableEvents = True

End Sub