This example makes use of conditional formatting and overwrites any existing conditional formatting on the sheet Also the code may clear the clipboard making it difficult to copy and paste on the sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iColor As Integer On Error Resume Next iColor = Target.Interior.Colorlndex If iColor < 0 Then iColor = 36

Else iColor = iColor + 1 End If

If iColor = Target.Font.Colorlndex 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

0 0

Post a comment