Excel - Shading entire row based on change of value

Solution 1:

What you can do is create a new column over on the right side of your spreadsheet that you'll use to compute a value you can base your shading on.

Let's say your new column is column D, and the value you want to look at is in column A starting in row 2.

In cell D2 put: =MOD(IF(ROW()=2,0,IF(A2=A1,D1, D1+1)), 2)

Fill that down as far as you need, (then hide the column if you want).

Now highlight your entire data set - this selection of cells will be the ones that get shaded in the next step.

From the Home tab, click Conditional Formatting, then New Rule.

Select Use a formula to determine which cells to format.

In "Format values where this formula is true" put =$D2=1

Click the Format button, click the Fill tab, then choose the color you want to shade with.

Examples here:

Solution 2:

I hate using these in-cell formulas and having to fill in a new column, and I finally learned enough to make by own VBA macro to accomplish this effect.

This might not be all that logically different from another answer, but I think the code looks a hell of a lot better:

Dim Switch As Boolean
For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)
    If Not Cell.Value = Cell.Offset(-1, 0).Value Then Switch = Not (Switch)
    If Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Pattern = xlNone
    If Not Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Color = 14869218
Next

My code here is going by column B, it assumes a header row so it starts at 2, and I use the Chr(x+64) method to get column letters (which won't work past column Z; I haven't yet found a simple-enough method for getting past this).

First, the boolean variable will alternate whenever the value changes to a new one (uses Offset to check cell above), and for each pass the row is checked for either True or False and colors it accordingly.

Solution 3:

I have found a simple solution to banding by content at Pearson Software Consulting: Let's say the header is from A1 to B1, table data is from A2 to B5, the controling cell is in the A column

1. Make a new column, C
2. At first the first row to color make the formula =true in the C2 cell
3. In the second row make the formula =IF(A3=A2,C2,NOT(C2))
4. Fill the column down to the last row
5. Select the data range
6. Select conditional formatting, choose Use a formula... and put =$C2 as the formula