Forum Discussion
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!
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.
4 Replies
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.
Thank you for this! If I wanted to apply this to a column of data, so each row is its own check (eg
Task 1 - date due in cell M2, date received N2
Task 2 - date due in cell M3, date received N3 etc etc)
how would I do this?
If you apply the formula of the solution without the $ signs then it won’t be fixed to “H4” and instead it varies in accordance.
So adapting the given solution to the cell references in your examples:Green =AND(N2<>””,N2<=M2)
Red =N2>M2
Applies to =$N$2:$N$10
So although in conditional formatting it is only M2 and N2 that is specifically referenced, excel should know when applying to a range to adjust accordingly.
- 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,
