Conditional formatting formula with multiple criteria

  • Thread starter Thread starter Kirstie
  • Start date Start date
K

Kirstie

Hi all,

Have been puzzling over this for a while now, and can't get it right!

Using conditional formatting, I want to:

If any of the cells in Column B are blank, and the cells in Column C are
blank, and the date in Column D is less than todays date - 3 days, turn the
cell bold and red

Can someone please help?

I have tried all different variations and just can't get it!

Thanks,

Kirstie
 
Kirstie

Your questions sound like you wish to pick up whether there is a blank
ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that
the date in a single cell in column D is more than three days old. The
reasons you may wish to test for this escape me but, here you go:

Add this to the conditional format of D2:

=AND(COUNTBLANK(B:B)>0,AND(COUNTBLANK(C:C)=65536,D2<TODAY()-3))

COUNTBLANK (obviously) counts blank cells.

So, if you have more than one blank cell in column B
AND
if you have 65536 blanks in column C
AND
The date in D2 is < today minus three days,

You will get a TRUE condition.

If you data fails to meet any one of the tests, you will get a FALSE
condition and the conditional format will not apply.

If I interpreted you question wrongly, hopefully COUNTBLANK() may help put
you on the right track.

HINT: When putting together complex conditional formatting, first construct
your formula in spare cells of you excel sheet. It saves a lot of
aggravation. If necessary, test each part of a multipart formula in separate
cells.
 
Why 2 ANDs? And better to use COUNT()=0 than COUNTBLANK()=65536

=AND(COUNTBLANK(B:B),COUNTA(C:C)=0,D2<TODAY()-3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Kirstie,

Here's my take on what I think you want.....

Select all of column D (note that when you select column D, D1 will be the activecell), and use
Format / Conditional Formatting..., with the "Formula is" option, and the formula

=AND(B1="",C1="",D1<>"",D1<(TODAY()-3))

Set your format to Bold and Red, etc.


HTH,
Bernie
MS Excel MVP
 
Ah ha!

This may be where I have been getting confused, and therefore confusing you.
I have been thinking of it in columns rather than rows, so i don't think it's
a complicated as i may have implied.

Having looked at it again, what i want to do is

if A2 is blank

and

B2 is blank

and

C2 is less than Today()-3

then

turn bold and red

Is that a bit more simple?

Thanks for helping me out!

Kirstie
 
CF with a formula of

=AND(A2="",B2="",C2<TODAY()-3)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Excellent, that did the trick.

Thanks all!

Bernie Deitrick said:
Kirstie,

Here's my take on what I think you want.....

Select all of column D (note that when you select column D, D1 will be the activecell), and use
Format / Conditional Formatting..., with the "Formula is" option, and the formula

=AND(B1="",C1="",D1<>"",D1<(TODAY()-3))

Set your format to Bold and Red, etc.


HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top