Changing cell colors based on date in another cell relative to today's date

[TJGypsy2]

Hi There,

I have a spreadsheet I use at work that I would like to modify to have cells change color automatically. We use this spreadsheet to track completion dates for projects. One of our audits has a deadline of 15 days to finish from a given calendar date. IE, the audit for the 1st has to be completed by the 15th.

What I would like to happen is for the cells in row 48 to change color. If today's date is 11-15 days from the date in row 6, I would like the cell to be red. If today's date is 6-10 days the date in row 6, I would like the cell to be orange, and if today's date is 1-5 days from the date in row 6, I would like the cell to be green. When a date is entered in row 48, I would like the cell to be white.

I'm pretty sure this can be done with conditional formatting, but I'm having a heck of a time making it work. I can make the cells change colors, but they don't go back to white when i enter a date, and I don't seem to be able to get to conditions to work. I'm fairly new to this particular aspect of Excel so I appreciate your help.

Riviera Black Hawk
Revenue Audit Tracking
Month/Year
	March-13
Revenue		Friday	Saturday	Sunday
Date		3/1/2013	3/2/2013	3/3/2013
Title 31/CTL	Audited By
Date
Reviewed By
Date

<tbody>
</tbody>

 

[konew1]

Why don't you post what you tried, and explain what happened.