Forum Discussion
Solved
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!
It truly is hard to give specific help without seeing the specific layout(s) of your workbook(s).
I've used the various COUNT functions recently with the FILTER function nested in it to select the rows to be counted. The latter is very new and requires the most recent version of Excel, but if you have it, it can be far more powerful than VLOOKUP. You could also try XLOOKUP.
Here's a reference that explains FILTER and a couple of other equally powerful and complementary functions. https://www.youtube.com/watch?v=9I9DtFOVPIg
By the way, your reference to multiple tabs concerns me. There are many times when we see posted here sample files where multiple tabs are used to represent, say, different customers or vendors, and the products associated. Excel works VERY well--you might say it excels--when such data, albeit for different customers or vendors or products, are ALL arrayed on a single sheet, in a single table, with columnar identifiers used to designate the customer or vendor or product (whatever it was that supposedly warranted its own sheet).
So that too could be part of a solution: consider consolidating those multiple tabs into a single database. You might be astounded at how much simpler you've made your task when you let Excel do the "heavy lifting" rather than trying to "help" by separating those entities out.
2 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 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,
