Using Conditional Formatting to Highlight Selected Cell

Submitted by Ivan F. Moala of Auckland, New Zealand. Ivan is the site author of "The XcelFiles" (, where you will find out things you thought you couldn't do in Excel.

Conditional formatting is used to highlight the row and column of the active cell to help you visually locate it. Important: Do not use this method if you already have conditional formats on the worksheet. Any existing conditional formats will be overwritten. Also, this program clears the clipboard, so it is not possible to use while doing copy, cut, or paste:

Const international As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iColor As Integer

'// On error resume in case

'// user selects a range of cells

On Error Resume Next iColor = Target.Interior.Colorlndex

'// Leave On Error ON for Row offset errors

Else iColor = iColor + 1 End If

'// Need this test in case font color is the same If iColor = Target.Font.ColorIndex Then iColor = iColor + 1


'// Horizontal color banding

With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)

.FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With

'// Vertical color banding

With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & _ Target.Offset(-1, 0).Address)

.FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With

0 0

Post a comment