SOLVED

# Conditional Formatting - red/green based on deadline date

Copper Contributor

# Conditional Formatting - red/green based on deadline date

Hello!

I am making a spreadsheet to track due dates for selection items for home building.  I want to be able to get a quick visual to see how progress is being made.  I have it set up so that H4 contains the due date in short date format, then in I4-I10 blank cells for me to input when an item was selected.  I have spent the last few hours trying to figure out how to turn the I cells red or green - if the short date entered in the I column is on or before the due date in H4, the I cell will turn green.  If the short date entered in the I column is after the due date in H4, the I cell (with the date) will turn red.  Does that make sense, and could someone guide me on this?

Thanks so much!

2 Replies
best response confirmed by emsplit (Copper Contributor)
Solution

# Re: Conditional Formatting - red/green based on deadline date

Select I4:I10.

I4 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(I4<>"",I4<=\$H\$4)

Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=I4>\$H\$4

and red as fill color.

# Re: Conditional Formatting - red/green based on deadline date

Thank you SO very much, Hans! That was something I had not yet tried, and it worked perfectly. Appreciate it, thanks again, and hope you have a great day,
1 best response

Accepted Solutions
best response confirmed by emsplit (Copper Contributor)
Solution

# Re: Conditional Formatting - red/green based on deadline date

Select I4:I10.

I4 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(I4<>"",I4<=\$H\$4)

Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=I4>\$H\$4

and red as fill color.