Forum Discussion
Conditional Formatting on whole column with variable reference cells
Thanks for responding! I couldn't get the below to work and I'm wondering if this is possible as there could be conflicting conditions...
I'm trying to change cell C based on today's date, unless there is a date in column D prior to the date detailed in column C.
I'll give you a working example:
- A contract is signed 17/01/22, and we expect a rep to have a follow up call with within 5 days "Expected date".
So B2 has sign off date of 17/01/22, and C2 is auto populated with 22/01/22 (=B2+5)
- As 22/01 approaches (within 3 days of this date) I want C2 to become yellow to alert rep action is needed, so I've used cell value between Today() and Today()+3 and formatted to yellow
- If no action is taken by 22/01/22, C2 should turn red, so I've used cell value <today() and formatted red
- But both yellow and red conditions should be superseded if column D "Date completed" has a date on or prior to expected date, when I'd like C2 to turn green
Here's a screenshot to help:
In this example:
- C2 should be green as date in D2 is prior to date in C2
- C3 should be red as date in C3 is equal or past today's date, with no prior date in D3
- C4 should be red as cell date is past today's date, and date in D2 was not prior to date in C2
- C5 should be yellow as cell's date is within 3 days of today's date
Any wizardry up your sleeve?
LShoesmith When I've done this before, I created a separate column to indicate that a task was completed. I then created an IF statement based on that completion column to indicate if the task was Done or the # of days remaining before it was due. For cell values that Contain 'Done', conditional formatting turns that green. The yellow and red conditions apply as normal.
Screenshot from file I use