Conditional Formatting with dates and highlights

[tmbaker]

I am writing a workbook and need a conditional formula that highlights due dates the month before they are due in yellow and stays yellow until the due date.  Then changes to a pink color if overdue.  I have a cell showing the current date, is there a way to use that cell in the formula?  

[#2 · Accepted solution]

you don't need the cell with the current date as that can just be in the formula.
Let's say the cells you want to check/highlight are D5:D100, highlight those cells.
Goto conditional formatting and choose to add a new custom using a formula.
The "Applies To" range will automatically be D5:D100 (the highlighted range)
write the formula as if you were writing it for the upper left cell in that range (D5) so:
=EDATE(D5, -1)<TODAY()
Change the formatting to be fill Yellow
now repeat with
=D5<TODAY()
and formatting pink (and make sure this rule take precedence over the other

[#3]

@m_tarler 

Thank you so much.