SOLVED

# Conditional Formatting with dates and highlights

Copper Contributor

# Conditional Formatting with dates and highlights

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 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Conditional Formatting with dates and highlights

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

# Re: Conditional Formatting with dates and highlights

Thank you so much.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Conditional Formatting with dates and highlights

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