Excel 2007 : Formatting a Cell based on Date in another

[RunNWild]

I am currently working on a spreadsheet in Excel 07, I need to color code one cell based on the date in another cell.

If the date in cell (A1) is greater than 7 days old, I need cell (D1) to turn Yellow, If greater than 10 days old, need cell (D1) to turn Red.

Any help you can provide is greatly appreciated.

Thanks,
~T

[JBeaucaire]

This is conditional formatting (you'll have to locate the menus in 2007).

Highlight D1 and activate the conditional formatting controls:

Condition1: Formula Is: =TODAY()-10>A1
Format...pattern...red

Condition1: Formula Is: =TODAY()-7>A1
Format...pattern...yellow

[RunNWild]

This is what is happening now:

I created the following: =B6+7>TODAY()
This is turning the cell red if the date in B6 is over 7 days from todays date, but I cannot figure out how to get the rows that have no date listed in the first cell not to turn red without entering a date. I only want this to execute if there is a date value in the first cell.
I should be able to highlight the cell with the formula in and copy it down the column to have it active on as many rows as I wish, correct??

[JBeaucaire]

Try this:

=AND(B6>0,B6+7>TODAY())

[RunNWild]

Same result, in rows with no date, the cell in the column with formula still changes color.

[JBeaucaire]

You can experiment with these formulas in a regular cell without having to keep going into the CF windows. In a cell, enter the CF formulas and play around. If the formula returns TRUE, it will trigger the color in CF, if FALSE it will not.

So, play with each piece of the formula, get each part to confirm TRUE/FALSE properly alone before you nest them together into an AND() statement. This is how I keep my sanity dealing with complex CF formulas.

Once it is working in a cell, then put it into the CF matrix.

[RunNWild]

I finally got it to work correctly. Thanks for the help JBeaucaire. Here is what I ended up using.

=IF(B6=0," ",B6+7>TODAY())