Excel - Shading entire row based on changer of value

[ForumBot]

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

[ForumBot]

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: