<p>I have an Excel Spreadsheet like this</p>

<pre>
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
</pre>

<p>Now I want to group the data of one id by alternating the background color of the rows</p>

<pre>
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
</pre>

<p>Can anyone help me with a macro or some VBA code</p>

<p>Thanks</p>

[{'answer_id': 27043, 'author': 'Daniel Pollard', 'author_id': 2758, 'author_profile': 'https://Stackoverflow.com/users/2758', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<p>Your logic might not be the same as your code above, it might look more like:</p>\n\n<p>Cell Value is | equal to |  | and | White   .... Then choose the color.</p>\n\n<p>You can select the add button and make the condition as large as you need.</p>\n'}, {'answer_id': 27069, 'author': 'csmba', 'author_id': 350, 'author_profile': 'https://Stackoverflow.com/users/350', 'pm_score': 1, 'selected': False, 'text': '<p>Do you have to use code?\nif the table is static, then why not use the auto formatting capability?</p>\n\n<p><img src="https://i.stack.imgur.com/7gJRz.jpg" alt="enter image description here"></p>\n\n<p>It may also help if you "merge cells" of the same data. so maybe if you merge the cells of the "data, more data, even more data" into one cell, you can more easily deal with classic "each row is a row" case.</p>\n'}, {'answer_id': 27139, 'author': 'Jason Z', 'author_id': 2470, 'author_profile': 'https://Stackoverflow.com/users/2470', 'pm_score': 3, 'selected': True, 'text': '<p>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.</p>\n\n<pre><code>Public Sub HighLightRows()\n    Dim i As Integer\n    i = 1\n    Dim c As Integer\n    c = 3       \'red\n\n    Do While (Cells(i, 2) &lt;&gt; "")\n        If (Cells(i, 1) &lt;&gt; "") Then    \'check for new ID\n            If c = 3 Then\n                c = 4   \'green\n            Else\n                c = 3   \'red\n            End If\n        End If\n\n        Rows(Trim(Str(i)) + ":" + Trim(Str(i))).Interior.ColorIndex = c\n        i = i + 1\n    Loop\nEnd Sub\n</code></pre>\n'}, {'answer_id': 6768304, 'author': 'Adriano P', 'author_id': 276311, 'author_profile': 'https://Stackoverflow.com/users/276311', 'pm_score': 5, 'selected': False, 'text': '<p>I use this formula to get the input for a conditional formatting:</p>\n\n<pre><code>=IF(B2=B1,E1,1-E1))    [content of cell E2]\n</code></pre>\n\n<p>Where column B contains the item that needs to be grouped and E is an auxiliary column. Every time that the upper cell (B1 on this case) is the same as the current one (B2), the upper row content from column E is returned. Otherwise, it will return 1 minus that content (that is, the outupt will be 0 or 1, depending on the value of the upper cell).</p>\n\n<p><img src="https://i.stack.imgur.com/H6WP0.png" alt="enter image description here"></p>\n\n<p><img src="https://i.stack.imgur.com/sE57q.png" alt="enter image description here"></p>\n\n<p><img src="https://i.stack.imgur.com/4hadL.png" alt="enter image description here"></p>\n'}, {'answer_id': 16961839, 'author': 'Laurent S.', 'author_id': 2187273, 'author_profile': 'https://Stackoverflow.com/users/2187273', 'pm_score': 2, 'selected': False, 'text': '<p>Based on Jason Z\'s answer, which from my tests seems to be wrong (at least on Excel 2010), here\'s a bit of code that happens to work for me :</p>\n\n<pre><code>Public Sub HighLightRows()\n    Dim i As Integer\n    i = 2 \'start at 2, cause there\'s nothing to compare the first row with\n    Dim c As Integer\n    c = 2       \'Color 1. Check http://dmcritchie.mvps.org/excel/colors.htm for color indexes\n\n    Do While (Cells(i, 1) &lt;&gt; "")\n        If (Cells(i, 1) &lt;&gt; Cells(i - 1, 1)) Then \'check for different value in cell A (index=1)\n            If c = 2 Then\n                c = 34   \'color 2\n            Else\n                c = 2   \'color 1\n            End If\n        End If\n\n        Rows(Trim(Str(i)) + ":" + Trim(Str(i))).Interior.ColorIndex = c\n        i = i + 1\n    Loop\nEnd Sub\n</code></pre>\n'}, {'answer_id': 28683156, 'author': 'KyleF', 'author_id': 4598660, 'author_profile': 'https://Stackoverflow.com/users/4598660', 'pm_score': 1, 'selected': False, 'text': '<p>I\'m barrowing this and tried to modify it for my use. I have order numbers in column a and some orders take multiple rows. Just want to alternate the white and gray per order number. What I have here alternates each row.</p>\n\n<p><code>\nChangeBackgroundColor()\n\' ChangeBackgroundColor Macro\n\'\n\' Keyboard Shortcut: Ctrl+Shift+B\nDim a As Integer\n    a = 1\n    Dim c As Integer\n    c = 15       \'gray\n    Do While (Cells(a, 2) &lt;> "")\n        If (Cells(a, 1) &lt;> "") Then    \'check for new ID\n            If c = 15 Then\n                c = 2   \'white\n            Else\n                c = 15   \'gray\n            End If\n        End If\n        Rows(Trim(Str(a)) + ":" + Trim(Str(a))).Interior.ColorIndex = c\n        a = a + 1\n    Loop</p>\n\n<p>End Sub</code></p>\n'}, {'answer_id': 30895802, 'author': 'AjV Jsy', 'author_id': 2078245, 'author_profile': 'https://Stackoverflow.com/users/2078245', 'pm_score': 0, 'selected': False, 'text': '<p>I have reworked Bartdude\'s answer, for Light Grey / White based upon a configurable column, using RGB values. A boolean var is flipped when the value changes and this is  used to index the colours array via the integer values of True and False. Works for me on 2010. Call the sub with the sheet number.</p>\n\n<pre><code>Public Sub HighLightRows(intSheet As Integer)\n    Dim intRow As Integer: intRow = 2 \' start at 2, cause there\'s nothing to compare the first row with\n    Dim intCol As Integer: intCol = 1 \' define the column with changing values\n    Dim Colr1 As Boolean: Colr1 = True \' Will flip True/False; adding 2 gives 1 or 2\n    Dim lngColors(2 + True To 2 + False) As Long   \' Indexes : 1 and 2\n          \' True = -1, array index 1.    False = 0, array index 2.\n    lngColors(2 + False) = RGB(235, 235, 235) \' lngColors(2) = light grey\n    lngColors(2 + True) = RGB(255, 255, 255) \'  lngColors(1) = white\n\n    Do While (Sheets(intSheet).Cells(intRow, 1) &lt;&gt; "")\n        \'check for different value in intCol, flip the boolean if it\'s different\n        If (Sheets(intSheet).Cells(intRow, intCol) &lt;&gt; Sheets(intSheet).Cells(intRow - 1, intCol)) Then Colr1 = Not Colr1\n        Sheets(intSheet).Rows(intRow).Interior.Color = lngColors(2 + Colr1) \' one colour or the other\n        \' Optional : retain borders (these no longer show through when interior colour is changed) by specifically setting them\n        With Sheets(intSheet).Rows(intRow).Borders\n            .LineStyle = xlContinuous\n            .Weight = xlThin\n            .Color = RGB(220, 220, 220)\n        End With\n        intRow = intRow + 1\n    Loop\nEnd Sub\n</code></pre>\n\n<p>Optional bonus : for SQL data, colour any NULL values with the same yellow as used in SSMS</p>\n\n<pre><code>Public Sub HighLightNULLs(intSheet As Integer)\n    Dim intRow As Integer: intRow = 2 \' start at 2 to avoid the headings\n    Dim intCol As Integer\n    Dim lngColor As Long: lngColor = RGB(255, 255, 225) \' pale yellow\n\n    For intRow = intRow To Sheets(intSheet).UsedRange.Rows.Count\n        For intCol = 1 To Sheets(intSheet).UsedRange.Columns.Count\n            If Sheets(intSheet).Cells(intRow, intCol) = "NULL" Then Sheets(intSheet).Cells(intRow, intCol).Interior.Color = lngColor\n        Next intCol\n    Next intRow\nEnd Sub\n</code></pre>\n'}, {'answer_id': 34124537, 'author': 'GONeale', 'author_id': 41211, 'author_profile': 'https://Stackoverflow.com/users/41211', 'pm_score': -1, 'selected': False, 'text': '<p>I use this rule in Excel to format alternating rows:</p>\n\n<ol>\n<li>Highlight the rows you wish to apply an alternating style to.</li>\n<li>Press "Conditional Formatting" -> New Rule</li>\n<li>Select "Use a formula to determine which cells to format" (last entry)</li>\n<li>Enter rule in format value: <strong><code>=MOD(ROW(),2)=0</code></strong></li>\n<li>Press "Format", make required formatting for alternating rows, eg. Fill -> Color.</li>\n<li>Press OK, Press OK.</li>\n</ol>\n\n<p>If you wish to format alternating columns instead, use <strong><code>=MOD(COLUMN(),2)=0</code></strong></p>\n\n<p>Voila!</p>\n'}]
['https://Stackoverflow.com/questions/27020', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2798/']