Conditional Formatting using =TODAY formula

Hello everyone,

I’m trying to create some formatting rules in Excel where the formatting rules include the =TODAY formula.

I’ve managed to reach the desired outcome for a single row (the formatted cell will appear red, orange, or green depending on how many days are left until today), but the problem is that the dates on each row change but the conditional formatting rules are static.

Is there a way for me to apply the conditional formatting formula dynamically so that it adjusts for the values in each row rather than being attached to the data in one specific cell?

Example:

Column A is the date a file was assigned. A1 is 15/04/2024

Column B is the date by which we need to have responded to that assignment. B1 is =A1+14 (we have 14 days to respond)

Conditional formatting is as follows:

First rule - =A1<=TODAY()-10 - if criterion applies, cell B1 will turn red

Second rule - =AND(A1<=TODAY()-5) - if criterion applies, cell B1 will turn orange

Third rule - =A1>TODAY()-5 - if criterion applies, cell B1 will turn green

This is great for knowing whether cell B1 is within those thresholds. But when I enter a new date into A2, the formatting in cell B2 is judged against the data inputs from A1. I need the formula to replicate across each row so that each date in column A is judged accordingly.

Hopefully that makes sense, and if anyone can provide any assistance at all then it would be greatly appreciated!