Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

alt text

share|improve this question
up vote 2 down vote accepted

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

End Sub
share|improve this answer
+1 Good stuff Paulo. Thank you! – p.campbell Jan 22 '10 at 21:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.