Format rows based on values in two cells in each row

Question

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?

JNevill · Accepted Answer · 2014-09-25 13:24:58Z

3

You need to apply the condition to every column in every row that you want highlighted. Also your formula shouldn't have the Rows locked with the $ symbol. It should read: =AND($C1=0, $D1=0)

edited Sep 25, 2014 at 13:24

answered Sep 24, 2014 at 18:36

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JNevill Over a year ago

Removed the superfluous parentheses.

pnuts · Accepted Answer · 2014-09-24 19:31:20Z

0

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

answered Sep 24, 2014 at 19:31

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Collectives™ on Stack Overflow

Format rows based on values in two cells in each row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related