Alternating coloring groups of rows in Excel

Question

Hi All,

I have an Excel Spreadsheet like this

id | data for id
   | more data for id
id | data for id
id | data for id
   | more data for id
   | even more data for id
id | data for id
   | more data for id
id | data for id
id | data for id
   | more data for id

Now I want to group the data of one id by alternating the background color of the rows

var color = white
for each row
    if the first cell is not empty and color is white
        set color to green
    if the first cell is not empty and color is green
        set color to white
    set background of row to color

Can anyone help me with a macro or some VBA code

Thanks

Answer 1

If you select the Conditional Formatting menu option under the Format menu item, you will be given a dialog that lets you construct some logic to apply to that cell.

Your logic might not be the same as your code above, it might look more like:

Cell Value is | equal to | | and | White .... Then choose the color.

You can select the add button and make the condition as large as you need.

Answer 2

I think this does what you are looking for. Flips color when the cell in column A changes value. Runs until there is no value in column B.

Public Sub HighLightRows()
    Dim i As Integer
    i = 1
    Dim c As Integer
    c = 3       'red

    Do While (Cells(i, 2) <> "")
        If (Cells(i, 1) <> "") Then    'check for new ID
            If c = 3 Then
                c = 4   'green
            Else
                c = 3   'red
            End If
        End If

        Rows(Trim(Str(i)) + ":" + Trim(Str(i))).Interior.ColorIndex = c
        i = i + 1
    Loop
End Sub

Answer 3

i wrote a specific answer this very problem using conditional formatting only in excel 2007 just the other day. find the how to step by step and screenshots at http://www.spyjournal.biz/node/903