VBA to highlight "sets" of rows according to incrementing field values

Question

I have a long chuck of code that successfully creates a large set of data.

The final task (CODE NOT WRITTEN) is to highlight all populated cols (A:BD) the same color, and for that color to change each time an identifier (in col B) changes. Each ID can have from 1-10 rows associated with it.

I need the colors to be pastel (so you can still read black text).

I am fine hardcoding a set of RGB in the code (maybe 10 colors), and just having the highlight loop through those colors in succession.

what I am trying to get to in psuedo:

c1 = RGB(R, G, B)
c2 = RGB(R, G, B)
c3 = RGB(R, G, B)
c4 = RGB(R, G, B)
c5 = RGB(R, G, B)

Sheet1.Range("A1:BD1").Interior.Color = c1

if b2=b1 Sheet1.Range("A2:BD2").Interior.Color = c1
   else Sheet1.Range("A2:BD2").Interior.Color = c2

if b3=b2 Sheet1.Range("A2:BD2").Interior.Color = c2
   else Sheet1.Range("A2:BD2").Interior.Color = c3

Answer 1

Try something like this:

Sub ColorRows()
    Dim rngColors As Range, rw As Range, i As Long, ws As Worksheet
    Dim rngToColor As Range, numColors As Long, clr As Long
    
    'setting the background color of each cell in this range...
    Set rngColors = ThisWorkbook.Worksheets("setup").Range("A1:A10") 'for example
    numColors = rngColors.Cells.Count 'how many colors
    
    Set ws = ActiveSheet 'or some specific worksheet
    Set rngToColor = ws.Range("A2:BD" & ws.Cells(Rows.Count, "B").End(xlUp).row)
    
    i = 1
    clr = rngColors(i).Interior.Color 'starting color
    For Each rw In rngToColor.Rows
        rw.Interior.Color = clr
        'value in B in next row is different?
        If rw.Cells(2).Value <> rw.Cells(2).Offset(1).Value Then
            i = i + 1
            If i > numColors Then i = 1 'loop back to the start?
            clr = rngColors(i).Interior.Color 'next color
        End If
    Next rw
End Sub