i wrote a worksheet selection change event script (below) to illuminate target cell location by changing the cell interior and font color on the row and column headers. the code works but i get a lot of screen flickering as it executes even with the screen updating turned off. i don't get any flickering when i turn this function off. i'd appreciate any comments or recommendations. thanks!
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmp As Range, A As Range, B As Range, C As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo z
Set A = Range("A6")
Set B = A.Offset(0, 1).End(xlToRight)
Set C = A.Offset(1, 0).End(xlDown)
'Reset font colors and interiors
With Range(A.Offset(0, 1), B)
.Interior.ColorIndex = 19
.Font.ColorIndex = 5
.Font.Italic = False
.Font.Bold = False
End With
With Range(A.Offset(1, 0), C)
.Interior.ColorIndex = 19
.Font.ColorIndex = 5
.Font.Italic = False
.Font.Bold = False
End With
'exit if in wrong area
If Target.Cells.Count > 1 Then GoTo z
If Target.Row <= A.Row Or Target.Column <= A.Column Then GoTo z
With Cells(A.Row, Target.Column)
.Interior.ColorIndex = 4
.Font.ColorIndex = 3
' .Font.Italic = True
.Font.Bold = True
End With
With Cells(Target.Row, A.Column)
.Interior.ColorIndex = 4
.Font.ColorIndex = 3
' .Font.Italic = True
.Font.Bold = True
End With
z:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub