Conditional formatting - color gradient based on another cell's value

[dro0p]

Hi All,

I'm struggling with a problem and hoping one of you can help me.

I'm organizing an event and I have a big tasklist in excel of things that have to be done by a certain duedate.
This original file was horribly outdated and a mess.. BUT it's functions and formulas were working.

I had to change the "look" of the file.. bot for some reason I can't get the conditional formatting on the due dates to work.

Explanation:

CELL A1 is the due date.
CELL B1 is the state. (OK, NOOK, BUSY, NA)
The event date is set to 2022-11-24

The date in CELL A1 is based on a little formula that we decided on by which date each task has to be completed on. (basically event date minus X days)

GOAL:

Whenever a state in CELL B1 is "BUSY" or "NOOK" we want to apply a gradient color on the CELL A1. based on how close we are on the due date.
(10 days before the due date it starts getting slightly yellow and more orange towards the due date, and when it passes the due date it should go towards red)
When the state in CELL B1 is "OK" or "NA" nothing should happen.

Any help would be MUCH appreciated.. I have been trying to fix this for days.. tried workarounds found on google.. but never could find the exact fix to my problem.

Thanks in advance!

Edit: added a test/sample file.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[dro0p]

I added a sample file.. Not sure if that makes sense.. I can't get it to work.

1. C2 = The event date.
2. Whenever something in column D is set to either "BUSY" or "NOK" I want a color gradient to happen on the cells in column C.
3. I want the color gradient to start off easy light orange 14 days before that date in column C, and gradually move towards heavy orange on the date itself and plain red 10 days after that date.

If anybody could help, would be greatly appreciated.
Tried many things.. but I can't figure it out.

Thanks in advance.

[JeteMc]

As for the conditional formatting rule for 'OK', 'NVT', <C2-14 and >C2+10 (when there should be no highlighting): =OR(D4="OK",D4="NVT",C4<C$2-14,C4>C$2+10)
For gradient colors: Set minimum to yellow and maximum to red.
Note that in order to show this I changed dates in C5:C20 so that they were within the range Nov. 8th to Dec. 8th of 2022.
Let us know if you have any questions.