Excel - Shading entire row based on change of value

Question

I would like to shade entire rows in Excel based on the value of one cell. For example say I have the rows below:

**File No** 1122 1122 1144 1155 1155 1155 1166 

I would like the first two rows (where the value of the file # is 1122) to be shaded in color 1, the next row (where the value of the file # is 1144) to be shaded in color 2, the next 3 rows (where the value of the file # is 1155) to be shaded color 1, the next row (where the value of the file # is 1166) to be shaded color 2

Answer 1

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.

Answer 2

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: