Excel: highlight groupings of data differently based on a cell's value

Question

Consider a sheet with a bunch of values grouped together. The goal is to have Excel shade the entire row's background color differently for each differing group of Requisition.

The color is not important. There could be 500 different color changes. I suspect the normal use case is around manually choosing the color. Given that the number of color changes is unknown, it may be hard to automate.

How can I best show logical groupings of rows in Excel? My first attempt was by row color, but perhaps there's a better way?

Answer 1

If you want to keep the color thing, you could automate by using two different colors, one for one group, other for the next group.

You can use the a little macro to achieve this.

Sub SetCustomColors()

    Dim color As OLE_COLOR
    Dim color1 As OLE_COLOR
    Dim color2 As OLE_COLOR

    Dim currentRequisition As Integer

    color1 = &HF0F0F0 ' RR GG BB
    color2 = &HF0F000 ' RR GG BB

    currentRequisition = -1
    For r = 3 To 16
        If currentRequisition <> ActiveSheet.Cells(r, 1).Value Then
            currentRequisition = ActiveSheet.Cells(r, 1).Value
            color = IIf(color = color1, color2, color1)
        End If
        ActiveSheet.Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, 4)).Interior.color = color
    Next

End Sub