Macro to change background colurs between two greys but over mutilple lines

[tonywatsonhelp]

Hi Everyone,
I need a macro not conditional formation for this please.

I have a range of data in sheet "Cals" Range S11:BX and lastrow.

Now Column S holds the clients name and all clients are sorted to be together.

what I need is a macro that can give me two Greys (pref with RGB numbers so I can edit them easily)
so look down column S and give all the rows with the first client grey, the next client the other grey etc then back to grey .

any ideas?
thank tony

 

[Yongle]

Try this

Sub Grey()
    Dim Cel As Range, Above As Range
    Range("S11").Interior.Color = RGB(150, 150, 150)
    For Each Cel In Range(Range("S12"), Range("S" & Rows.Count).End(xlUp))
        Set Above = Cel.Offset(-1)
        Cel.Interior.Color = Above.Interior.Color
        If Cel <> Above Then
            With Cel.Interior
                If Above.Interior.Color = RGB(150, 150, 150) Then .Color = RGB(205, 205, 205) Else .Color = RGB(150, 150, 150)
            End With
        End If
    Next Cel
End Sub

 

[Yongle]

Above colours column S ony
To apply colour to columns S:BX, amend one line

With Cel.Interior
With Cel[COLOR=#ff0000].Resize(, 58)[/COLOR].Interior

 

[Fluff]

How about

Sub tonywatson()
   Dim Cl As Range, Strt As Range
   Dim Clr As String
   
   Clr = RGB(203, 203, 203)
   Set Strt = Range("S11")
   For Each Cl In Range(Range("S12"), Range("S" & Rows.Count).End(xlUp))
      If Cl.Value <> Strt.Value Then
         Range(Strt, Cl.Offset(-1)).Resize(, 58).Interior.Color = Clr
         Set Strt = Cl
         Clr = IIf(Clr = RGB(203, 203, 203), RGB(128, 128, 128), RGB(203, 203, 203))
      End If
   Next Cl
End Sub

 

[Yongle]

Here is how to apply to all columns S:BX
Have included a variable for each colour, which makes it easier to amend as you requested

Sub Grey()
    Dim Cel As Range, Above As Range, C1, C2
    C1 = RGB(150, 150, 150)
    C2 = RGB(205, 205, 205)
    Range("S11").Resize(, 58).Interior.Color = C1
    For Each Cel In Range(Range("S12"), Range("S" & Rows.Count).End(xlUp))
        Set Above = Cel.Offset(-1)
        Cel.Resize(, 58).Interior.Color = Above.Interior.Color
        If Cel <> Above Then
            With Cel.Resize(, 58).Interior
                If Above.Interior.Color = C1 Then .Color = C2 Else .Color = C1
            End With
        End If
    Next Cel
End Sub

Please ignore post#3

 

[tonywatsonhelp]

Hi Yongle,
thanks for that last addition it was just what I wanted and have the two colours at the top means I can easily use it for other projects so thank you very much.
Fluff, thanks for your help also, is always good to see alternative ways of writing code as this helps me learn.
Big thanks to both of you for helping me sort this out

Tony

 

[Yongle]

Thanks for your feedback