Applying Conditional Formatting (by 3 conditions)

Referring to the attached, for each range of B2:B26 & D2:D26, I’m trying to apply conditional formatting when there are three conditions meet:

• If there’s blank cell in the range (B2:B26 for instance)
• Each equivalent cell in range A2:A26 isn’t blank
• If the whole range (B2:B26 for instance) isn’t blank

I’ve reached the stage of conditionally formatting to meet the first two conditions over the two ranges by applying this formula: =(\$A2<>"")*(\$B2=""), for the range B2:B26 and =(\$A2<>"")*(\$D2=""), for the range D2:D26.

But I’m not able to include the third condition whatsoever, any suggestion here please?

Files

• Hello,

Specifically for your third point ... you could use

=COUNTBLANK(\$B\$2:\$B\$26)>0

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Alright, let us take this range only to simplify, \$B\$2:\$B\$27.

Now, applying the below three conditions for example:
=(\$A2<>"")
=(\$b2="")
=COUNTBLANK(\$b\$2:\$b\$27)<>COUNT(\$C\$2:\$C\$27)

They are supposed to be all TRUE for cell B3 only, so the conditional formatting should work for this cell only.

However, when I apply them in one condition to apply the formatting by this way:
=(\$A2<>"")*(\$b2="")*COUNTBLANK(\$b\$2:\$b\$27)<>COUNT(\$C\$2:\$C\$27)
The formatting is applied over the whole range, which is not supposed to be the case

Why is that?

• Hi again,

Attached is your Test file ...

Hope this will help

Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Wow! Oddly this worked!
First of all, let me thank you of course

The only difference I could tell that you didn't absolute the column:
My formula was:
=(\$A2<>"")*(\$b2="")*COUNTBLANK(\$b\$2:\$b\$27)<>COUNT(\$C\$2:\$C\$27)