Conditional Formatting and referencing cell

[SUPPO_USN]

Hello all,

I've come across a problem I'm having trouble resolving. I am trying to change the cell background color when the date in the cell is between 75 and 90 days of the current date. I've used the following code in the conditional formatting:

=(TODAY()-$K$22>75)*AND(TODAY()-$K$22<90) and set the formatting if true to turn the cell yellow.

K22 is a date. This code works just fine. My issue is I have MANY cells that I want to apply this Conditional Formatting to and the task of going to each and every cell to change the $K$22 to the correct cell reference is just crazy.

I'm looking for a coding that will let me highlight all the cells I want to apply the formatting to and set it that way.

I've tried

=(TODAY()-cell.value>75)*AND(TODAY()-cell.value<90)

and it didn't work. any tips on how to resolve this matter would be greatly appreciated.

mainly I'm trying to turn the cell color yellow if the date currently held in that cell is between 75 and 90 days prior to today's date. I can do it to each cell individually, I'm just trying to save myself hours of time if there's a simple formula I can use to format all of them at once...

any help will be appreciated.

[ConneXionLost]

Have you tried using relative references in your formula?

Cheers,

[MarvinP]

Hi
see the attached with an example of how it works.

[SUPPO_USN]

Thanks Marvin,

that did what I wanted it to do with a little modification. namely changing the today() +70 to today()-70 since the cell dates were in the past, but regardless, thanks. I was trying to make it too complicated on myself.

[MarvinP]

Great !!

Conditional Formatting and Validation rules always take me a few Trial and Error turn-a-rounds before they work.

Glad you understood and made it work for you.