This article was adapted from MrExcel.com. Visit the MrExcel.com Web site for more tips and information.

In order to help visually locate the active cell pointer, is there a way to have a temporary color band appear in the current row and column?

The following is an elegant solution to this question.

Important  This solution makes use of conditional formatting and Visual Basic for Applications (VBA), and will overwrite any conditional formats that you have on the worksheet. Do not use this method if you already have conditional formats on the worksheet.

What it does:

• The code will highlight the current row and column, up to the cell pointer in a light yellow color.
• As you move to a new cell, the highlights move with the cell pointer.
• If the cell pointer moves to a cell that is already yellow, the highlights change color.

This effect is accomplished by using the Worksheet_SelectionChange event handler. The following code must be pasted on to the Worksheet code module. Every time that the cell pointer is moved to a new location, the code will delete all conditional formats on the worksheet, and assign a new conditional format on the fly to the cells in the current row and column.

There are two drawbacks to this method. First, as mentioned above, it is not appropriate if you already have conditional formats. Second, the code tends to clear the clipboard, so it becomes virtually impossible to copy and paste while this code is running.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer

'// Note: Don't use if you have conditional
'// formatting that you want to keep

'// On error resume in case user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex

'// Leave On Error ON for Row offset errors
If iColor < 0 Then
    iColor = 36
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
Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="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)  'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub