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"
ayesamson 