Alternate Row color when value changes using 2 colors

robertwp7472

robertwp7472

New member
Joined
Jul 22, 2016
Messages
86
Reaction score
0
Points
0
Location
Fox Island, WA
Hello All Gurus,

I have been working on a sub that will change the row color when the value in Column C changes. I need it to alternate between two different colors every time the value changes starting with Row 3. Column C has repeating values and then it'll change, repeat again, etc. Like this:

8081837591 Yellow
8098922005 White
8098922005 White
8098922005 White
8098866848 Yellow
8098866848 Yellow
8099154851 White
8098281091 Yellow
8098281093 White
8098941580 Yellow
8099018602 White
8099018602 White
8098082163 Yellow
8098385731 White
8098385732 Yellow
8098385732 Yellow
8098385732 Yellow
8098385732 Yellow
8098686076 White

I am trying to use a piece of code I found on Stack Overflow's website which according to the instructions should work but I keep getting errors. Here is what I have:
Code: 
Private Sub ShadeRows()
    Dim Order As Long, NxtOrder As Long, lr As Long, R As Long
    Dim Clr As Integer

Application.ScreenUpdating = False
    
    lr = ActiveSheet.Range("C3" & Rows.Count).End(xlUp).Row

    ' Enter desired color codes here
    ' (19 is Pastel Yellow, 1 is White)
   [COLOR=#ff0000] RwColor[/COLOR] = Array(19, 2)

    Clr = 0  ' Used to toggle between the two colors

    For R = 2 To lr
        [COLOR=#ff0000]Order = Cells(R, 1).Value[/COLOR]
        NxtOrder = Cells(R - 1, 1).Value
        If Order <> Nxtorder Then Clr = 1 - Clr

        ' Select only the columns that are used
        Range("A" & R & ":L" & R).Interior.ColorIndex = RwColor(Clr)
    Next R

Application.ScreenUpdating = True

End Sub

The error says "Variable Not Defined" So I tried adding "Dim RwColor as Variant" and then it got stuck on the next line saying "Object not defined". I tried a few different thing but each time the it kept throwing different errors; now I'm just frustrated.

What am I missing in the Syntax? Any help is greatly appreciated.
 
It might be easiest to use conditional formatting for this.
Select C2 and set one format condition to =(MOD(SUMPRODUCT(1/COUNTIF($C$1:$C1, $C$1:$C1)), 1) = 0) (yellow)
and another to =(MOD(SUMPRODUCT(1/COUNTIF($C$1:$C1, $C$1:$C1)), 1) = 1) (white)

Then copy that conditional formatting to the rest of the column.

Actualy you might want to use
=MOD(IFERROR(SUMPRODUCT(1/COUNTIF($C$1:$C1,$C$1:$C1)),0.5),2)=0 instead, to handle the blank cells at the bottom of the column

If you really want a VB routine then try this

Code: 
Sub test()
    Dim oneCell As Range
    Dim CellColor As Long
    
    CellColor = vbYellow
    
    Application.ScreenUpdating = False

    For Each oneCell In Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
        oneCell.Interior.Color = CellColor
        If oneCell.Value <> oneCell.Offset(1, 0).Value Then
            If CellColor = vbWhite Then
                CellColor = vbYellow
            Else
                CellColor = vbWhite
            End If
        End If
    Next oneCell

    Application.ScreenUpdating = True
End Sub
 
Last edited:
Thank you Mike. The VB works great. I made a couple of minor modifications to it so that it used my color palette, I added an EntireRow qualifier, and changed the offset to begin on Row 3. Thank you for your help. :cool2:

Here is what I ended up with:
Code: 
Private Sub colorize()

    Dim oneCell As Range
    Dim CellColor As Long
    
    CellColor = 13434879
    
    Application.ScreenUpdating = False

    For Each oneCell In Range(Cells(3, 3), Cells(rows.Count, 3).End(xlUp))
        oneCell.EntireRow.Interior.Color = CellColor
        If oneCell.Value <> oneCell.Offset(1, 0).Value Then
            If CellColor = 16777215 Then
                CellColor = 13434879
            Else
                CellColor = 16777215
            End If
        End If
    Next oneCell
    
    Application.ScreenUpdating = True
    
End Sub

Again Thank you
 
You must log in or register to reply here.
Back
Top