Macro To Colour In Rows

[Dazzawm]

I have upto 10000 rows with data in. I need the macro to look at the data in column 'C' and whenever the data changes I need it to change colour and colour in the whole row.

i.e C1:C10 has the same data and the macro colours in the rows red, C11:C20 the data is different so the rows get coloured yellow and so on. I know if there are 10000 rows it would be a lot of colours, but after say using 10 colours in can start again.

Thanks.

 

[RSXchin]

Is it ALWAYS every 10 rows?

 

[Dazzawm]

No some may be 4, some maybe 15! They are all random.

 

[RSXchin]

You'll have to choose more colors, but I think this'll work. If you can't

Sub color_the_rows()
    Dim Colors As Variant, SameData As String, i As Long, j As Integer
    Set Colors = Array(vbRed, vbGreen, vbYellow, vbBlue, vbWhite,vbMagenta,vbCyan)
    SameData = Cells(1, 3)
    For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
        If Cells(i, 3) <> SameData Then
            j = j + 1
        End If
        If j = 7 Then
            j = 0
            SameData = cells(i,3)
        End If
        Rows(i).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = Colors(j)
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Next i
End Sub

 

[Dazzawm]

An error has come up, pointing at the colours part.

 

[RSXchin]

See below

 

[RSXchin]

USE THIS ONE!!! I'm kinda tweaking on caffeine right now

Sub color_the_rows()
    Dim Colors As Variant, SameData As String, i As Long, j As Integer
    Colors = Array(vbRed, vbGreen, vbYellow, vbBlue, vbWhite, vbMagenta, vbCyan)
    SameData = Cells(1, 3)
    For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
        If Cells(i, 3) <> SameData Then
            j = j + 1
            SameData = Cells(i, 3)
        End If
        If j = 7 Then
            j = 0
        End If
        Rows(i).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = Colors(j)
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Next i
End Sub

 

[Dazzawm]

Thats it thanks, I was just writing that it was only colouring every other row when you sent me another. Thanks for your help.

 

[RSXchin]

one more thing... if you ever decide to add in extra colors, or lessen them.

change this line

If j = 7 Then

to this

if j > ubound(colors) then

 

[Dazzawm]

What about if I want to change the column? Is that wherever there is a 3 in the code? So column D I would change to a 4 and so on?