Applying Conditional Formatting (by 3 conditions)

  • Hi guys, I seek your help please.
    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?

  • Hello,


    Not sure to understand your logic nor your final objective ...


    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?

  • 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)
    Your formula is:
    =($A2<>"")*(b2="")*COUNTBLANK($b$2:$b$27)<>COUNT($C$2:$C$27)

    but I don't get it, is there an explanation for that?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!