# 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 "Thumbs Up" 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 "Thumbs Up" 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)