Search code examples
excelexcel-formulaworksheet-functionconditional-formatting

Format rows based on values in two cells in each row

I am trying to apply formatting conditionally:

If in row 1, C = 0 and D = 0 highlight the entire row 1, then repeat for each row.

I have the following formula:

=(AND($C$1=0, $D$1=0))

but with this I have two issues:

  • the formatting is applied just in row 1, and not subsequent rows
  • only the column I am applying the conditional formatting to is highlighted, rather than a complete row.

How can I achieve my goal?

Solution

  • A slight variation on @JNevill's answer is to add provision to limit the CF to rows where C and D are not both blank (or your sheet may have a lot of highlighting if you do not limit the row range - and if you do limit the row range at some time in future it may not be sufficient). Blank cells trigger an =0 condition.

    So a CF formula such as: 

    =AND($C1=0,$D1=0,$C1<>"",$D1<>"")

    and an Applies to range such as: 

    =$A:$Z

    or further "east" to suit:

    SO260224013 example