Conditional Formatting on whole column with variable reference cells

ExcelTrish 

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?

LazyBee 

Since you apply CF to entire column D, your formula shall be for the first cell of the range, other words for D1, otherwise the logic will be shifted. I'd suggest

=$D1<OFFSET($D1,-1,1)

and