ayesamson

It’s pretty easy to highlight every other row in Excel using a condition. In the matters of highlighting a row based on a value of single cell is another story. Not entirely complex but nonetheless requires some knowledge of writing formulas and knowledge of the INDIRECT function. Bastien blogged about Conditional row color based on a cell value which illustrates the process quite nicely. The steps I am about to cover mimic Bastien’s procedures with the exception of targeting Excel 2007 instead of Excel 2003.

Sample data set

Step 1: Highlight the rows

You will need to highlight the rows that are targeted in which you wish to apply the conditional formatting.

Step 2: Click on Conditional Formatting

Conditional Formatting is found under the Home tab of the Ribbon.

Step 3: Select Manage Rules

Step 4: Click New Rule

Step 5: Select Use a formula to determine which cells to format

Step 6: Enter the following formula which uses the INDIRECT function

=INDIRECT("C"&ROW())="M"

Then click the Format button to specify the formatting options

Step 7: Click the Fill tab

Step 8: Select the background color

Once you have selected a color click the OK button to proceed

Step 9: Click OK

Step 10: Click OK to apply formatting

Final results

To highlight the rows that have a Gender value of F in column C simply repeat steps 4 – 10 and switch the conditional value to F as in (Step 6)

Formula Syntax:

=INDIRECT("C"&ROW())="F"