Excel Conditional Formatting - format cell based on row number AND formula

Question

I'm trying to conditionally format cells individually that are above a certain value AND on an even row number. I can perform these tasks separately, but the AND function will not work. I believe it is due to Excel trying "compute" a range comparison inside the AND, and it produces 0.

Here is an image of what I'm trying to achieve:

basic · Accepted Answer · 2020-10-01 00:47:55Z

1

Use formula

=AND(B2>0.5,MOD(ROW(),2)=0)

and apply to range $B$2:$C$4

answered Oct 1, 2020 at 0:47

basic

12k2 gold badges15 silver badges29 bronze badges

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

3 Comments

likethevegetable Over a year ago

Why do I need to reference an individual cell here whereas for the first example I show, the range works?

basic Over a year ago

Range works, but not the way you think. In the first example, CF takes the first cell from the range and compares it to 0.5, i.e. cell B2 uses the formula B2:C4>0.5 => B2>0.5 => TRUE, cell C2 uses C2:D4>0.5 => C2>0.5 => FALSE, etc. In the third example, CF takes the entire range, divides it into cells, and compares each to 0.5, then returns the result to the AND function, i.e. cell F2 uses

AND(F2:G4>0.5,MOD(ROW(),2)=0) => AND(F2>0.5,G2>0.5 ... F4>0.5,G4>0.5, MOD(ROW(),2)=0) => AND(TRUE, FALSE ... FALSE, TRUE, TRUE) => FALSE

likethevegetable Over a year ago

Thanks, I think I understand the conditional formatting syntax a lot better now. Cheers.

Collectives™ on Stack Overflow

Excel Conditional Formatting - format cell based on row number AND formula

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related