Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

Makes use of conditional formatting and overwrites any existing conditional formatting on the sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)     Dim iColor As Integer     On Error Resume Next     iColor = Target.Interior.ColorIndex     If iColor < 0 Then         iColor = 36     Else         iColor = iColor + 1     End If     If iColor = Target.Font.ColorIndex Then iColor = iColor + 1     Cells.FormatConditions.Delete     With Range("A" & Target.Row, Target.Address)         .FormatConditions.Add Type:=2, Formula1:="TRUE"         .FormatConditions(1).Interior.ColorIndex = iColor     End With     With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & _         Target.Offset(-1, 0).Address)         .FormatConditions.Add Type:=2, Formula1:="TRUE"         .FormatConditions(1).Interior.ColorIndex = iColor     End With End Sub