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
This question is related to
excel
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:
