Highlighting rows in Microsoft Excel Based on a cell value

14 Feb 2008 by Chetan Shah.

For example :
Set the background color of Row 1 to Blue if the cell value of B1 is 5
Set the background color of Row 1 to Red if the cell value of B1 is 10
Set the background color of Row 1 to Orange if the cell value of B1 is 15

Following are the steps to highlight the entire row based on a cell value.

1. Select the entire Row (Row 1 in our case)
2. Go to Format –> Conditional Formatting
3. In Condition 1, Select “Formula is” from the first select box.
4. Enter “=IF($B1=5,1,0)” in second select box.
5. Click the “Format” button
6. Click on “Patterns” tab.
7. Select blue color.
8. Click ok
9. Click “Add” button
10. Follow steps 3-8, the only change is : in the formula enter 10 instead of 5 and select red color.
11. Click “Add button
12. Follow steps 3-8, the only change is : in the formula enter 15 instead of 5 and select orange color.
13. Done

If you want to apply it all rows in the sheet.

1. Complete the above steps. This will setup first row
2. Select first row
3. Click on “Format Painter” icon from your Standard Toolbar
4. Select all the rows on which you want to apply Step 1’s formatting.
5. Done!

Posted in Technology | 1 Comment »