Conditional Formatting with a date range

[jason.hampton]

All,

I am attempting to create a formula (to no avail) for Column E; for dates older than 90 days, and future. Let me explain. In the attached workbook you will notice I have several rules for Column E. The ones I am having trouble with are for "Company 3 & 4".

My goal would be to have a formula for dates in Column E greater than 90 days to be indicated with red fill (Company 3, cell E4); and to create a formula to indicate if a future date was entered with some other color (Company 4, cell E5).

Cells F1:G1 are for Today's Date.

Any help would be appreciated. I've searched the forums and found many other great ideas; just not one I can modify to fit my situation.

Thanks in advance.

[bebo021999]

Create a 4th conditional formating by
New rule/ Use a formula to ...
type the formula:
=ABS($F$1-E2)>90
Choose the format
Apply to E:E

[Pete_UK]

It's not really necessary to apply those CF rules to a whole column, so you might like to select cell E2 and click on Conditional Formatting | Manage rules and change each of the ranges for your existing 3 rules in turn and make them $E$2:$E$100 for example, then click OK or apply.

Select the range of cells that you want the new conditions to apply to (i.e. from E2 to E100), and then click on CF | New Rule | Use a Formula... , then enter this formula in the dialogue box:

=AND(ISNUMBER($E2),$E2>$F$1)

Click on the Format button | Fill tab and choose the colour that you want for future dates (e.g. Blue) then click OK twice to exit the dialogue box.

Then repeat, but use this formula in the dialogue box:

=AND($E2<>"",$E2<=$F$1-90)

and choose Red for this condition.

When you exit the CF dialogue, Excel will automatically adjust the cell ranges to suit the range that you had selected. If you need more cells to apply the conditions (there are now 5 of them), then you can use the Format Painter icon.

Hope this helps.

Pete

[jason.hampton]

Pete / Bebo,

Your feedback is much appreciated! Thanks again.