IF and AND statements in conditional formatting

**daowen** · 02-16-2012, 09:54 AM

I have attached a spreadsheet that I am working on to make my life a little easier. I have macros in it for the calendar function on three cells.

I need to have the spreadsheet do a conditional formatting on the cells.

Example - Row 4

B4 contains open date
C4 contains follow up date
D4 contains closed date

Condition - If C4 = blank, and today - B4 > 14 then format row red between A4 and E4, but if Today - C4 < 4 don't format redReversals Template Ver 2.xls

Any help would be greatly appreciated.

Regards,

Doug

**NBVC** · 02-16-2012, 10:02 AM

The first parts says the Red formatting will apply if C4 is blank along with today - B4>14, so then the Today-C4>4 check shouldn't be necessary

, should it?

=AND($C4="",Today()-$B4>14)

**daowen** · 02-16-2012, 10:47 AM

If C4 is blank the then it should check to see if today - B4 is greater than 10 and if so line turns red, if C4 has a value then if today - C4 is greater than 4 the line turns red.

That is what I am trying to do.

Doug

**NBVC** · 02-16-2012, 10:55 AM

Try:

=OR(AND($C4="",Today()-$B4>14),AND($C4<>"",Today()-$C4>4))

**daowen** · 02-16-2012, 11:28 AM

You are the man!

Works GREAT! Thanks. Looks like another bump on your rep on that one!

Now how do I set it so that is copied to all the cells counting down to 1000?

Doug

**NBVC** · 02-16-2012, 11:31 AM

You can either copy and paste special|Formats or preselect the range to colour and apply the formula I gave to the whole range, the row references will auto update for each selected row.

**daowen** · 02-16-2012, 11:34 AM

I would say that is fantastic. Thanks for your help on this one NBCV, my brain was coming up blank on it.

Dogu

**MAZE03079** · 07-25-2013, 12:16 PM

I'm having problems and I'm not sure what to do ... I'm trying to set up 3 CF's.

I'm trying to use the following CF's:

1. IF the date in Column G is BLANK and the date in Column H is <TODAY()-1 then the text color should be RED ... the formula I'm using is:
=AND(G2="",H2<TODAY()-1)

2. IF the date in Column G is BLANK and the date in Column H is =TODAY() then the text color should be BLACK ... the formula I'm using is:
=AND(G2="",H2=TODAY())

3. IF the date in Column G is BLANK and the date in Column H is <=TODAY()+14 and >TODAY()+1 then the text color should be ORANGE ... the formula I'm using is: =AND(G2="",H2<=TODAY()+14,H2>TODAY()+1)

If there is a date entered in Column G then there's no CF in Column H

Unfortunately excel doesn't seem to like this. I've created a separate columns to see if what I'm doing will at least give me TRUE or FALSE answers and they all work for the different scenario's ... but they don't work when I use them in the CF editor.

Any help will be greatly appreciated ...