12/02/2013

Excel - Switching Row Foreground Color for each new value


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