Ok. Ladies and gentlemen. In Excel you have a “driver” column that has a limited number of values: marks, ranks, status and like. You need to color entire row based on the value in that column.
Conditional formatting is the answer.
– assumes your data starts from second row;
– uses column C as a “driver” column;
– add 2 conditions Less than 4 and Greater then 7.
-01- Highlight the entire range of cells that need coloring (it means all rows and all columns that will be included in coloring).
-02- Pick Conditional Formatting on Home tab, Styles section and pick New Rule.
-03- Pick the last option – Use formula to determine witch cells to format.
In the box “Format values where this formula is true” type this:
=IF($C2 < 4, 1,0)=1
Note: This formula is satisfied and evaluates to true, when value in your “driver” column “C” is less than 4. Missing dollar sign in fromt of 2 (in $c2) means that you want your condition to change for every row.
-04- Set fill format to pink
-05- Repeat the same steps for values greater than 7. Use formula:
=IF($C2 > 7, 1,0)=1
And set fill color to light green.
You are done! Just look at your magnificent work.
Note: if you need to set a rule for values between 4 and 7, use this more elaborate rule: