'Anil' Radhakrishna's Code Gallery

Home | Blog | Contact

HOW TO conditionally format entire row/s based on a text value in a cell in Excel 2007

This step by step article shows how to conditionally format an entire row or range of rows based on a text value in a cell. In the example below we will specify a different color to an entire row based on whether the column B contains the text "BrandA" or "BrandB" or "BrandC"


1) Select the entire range of rows for which you want to apply specific background colors.

2) In the Home tab, click on Conditional Formatting item in the Styles panel

3) Select the last option Manage Rules



4) This brings up the Conditional Formatting Rules Manager.


5) Click on the New Rule button


6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".



7) In the textbox that says Format values where this formula is true, type this formula:

=EXACT($B1,"BrandA")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.

8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells

9) Move to the last tab Fill, select a color that you want to mark as the background color (I choose green) and then click on OK


10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "BrandA".

11) To specify a background color for rows that contains the text value "BrandB", repeat steps 3 to 9, this time specifying a different background color (I choose blue)

12) Repeat this procedure for other criteria (text value "BrandC") and finally you have entire rows conditionally formatted.


References:
Conditional Formatting