How to highlight active row and column?

R

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.
 
J

JMB

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
 
J

JMB

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top