Conditional formatting dates in 2007

[Lucy]

Hi I am working in excel 2007.
I have to columns filled with dates down to row 35. I want to set the
background of the cells to red if the dates shown are less than today. I can
get this to work for one cell with this formula:
=$D$2<=TODAY()
So far I have been creating a new rule for each cell. Once I got down to
cell D14 Excel crashed as it couldn't handle 14 conditional formats. I've
tried to set one conditional format for all the cells but then this sets all
the cells background to red (which shouldn't happen as most of the dates are
in the future so far). Can anyone help me out please?

 

[Peo Sjoblom]

I am not using 2007 at the moment but it sounds odd that you create a new
rule for each cell if the conditional formatting is the same, just copy the
formatting to the other cells and use one rule


--


Regards,


Peo Sjoblom

 

[T. Valko]

Assume your dates are in the range D2:E37

Select the range D2:E37

Goto Home tab>Styles>Conditional Formatting>New Rule>Use a formula to
determine.....

=AND(D2<>"",D2<=TODAY())

Click the Format button
Select the desired style(s)
OK out

 

[Lucy]

That is what I was trying to do - create one formula for all the cells.
I was originally entering code similar to this:

=$F$2:$F$35<=TODAY()

But this resulted in all the cells being formatted (which was wrong).

I did not know what the correct formula would be.

 

[Lucy]

Thank you for giving me the correct formula!

Assume your dates are in the range D2:E37

Select the range D2:E37

Goto Home tab>Styles>Conditional Formatting>New Rule>Use a formula to
determine.....

=AND(D2<>"",D2<=TODAY())

Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you for giving me the correct formula!