This code permit to switch the text color each time a row have a different value.
Here the column considered for changement is the column "B"
cheers
Sub test()
Dim currWCRFCode$
Call InitilizeColor
currWCRFCode = Range("B2").Value
For i = 2 To Range("B65536").End(xlUp).row
If (currWCRFCode <> Range("B" & i).Value) Then
Call incrementColorIndex
End If
Call setFont(i)
currWCRFCode = Range("B" & i).Value
Next i
End Sub
Private Sub setFont(ByVal row As Long)
Rows(row & ":" & row).Select
With Selection.Font
.color = getColor()
.TintAndShade = 0
End With
End Sub
Private Function getColor() As Long
getColor = colorsArray(currColorIndex)
End Function
Private Sub incrementColorIndex()
If (currColorIndex = 2) Then
currColorIndex = 0
Else
currColorIndex = currColorIndex + 1
End If
End Sub
Private Sub InitilizeColor()
currColorIndex = 0
colorsArray(0) = RGB(0, 0, 0)
colorsArray(1) = RGB(152, 14, 138)
colorsArray(2) = RGB(28, 152, 14)
End Sub