Conditional Formatting - red/green based on deadline date

Copper Contributor



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)


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


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


Repeat these steps, but with the formula


and red as fill color.

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,