Today or Before Conditional Formatting

[jswillcox]

I am having a problem with conditional fomatting.

I have created a rule "=$I$14<=TODAY()" which does exactly what I want to do.

My issue is when I apply this same rule to a number of cells "=$I$4:$I$66<=TODAY()" it will either format all or none. It will not format each cell individually.

Question: What can I do other than create a rule for each individual cell?

 

[Gerald Higgins]

Maybe you want to leave the rule as on line 2 of your post, but make it apply to the range I4:I66.

 

[FDibbins]

As suggested, you need to use a single cell ref for that test, not a range - it is looking down the entire range and seeing is any of them need the criteria

Also, hopefully your formula does not really have "" around it?

 

[jswillcox]

As suggested, you need to use a single cell ref for that test, not a range - it is looking down the entire range and seeing is any of them need the criteria

Also, hopefully your formula does not really have "" around it?

I don't have "" around the formula.

I have tried just using the formula =$I$14<=TODAY() and applied it to multiple cells. Unfortunatlly they are still all changed or non of them are. Any additional thoughts?

 

[MARZIOTULLIO]

Hi

try

=$I4<=TODAY() applies to =$I$4:$I$66

 

[jswillcox]

That was it!! Thank you for the help!

Do you know why removing the additional $ fixed the issue? fromula =$I4<=TODAY() worked while =$I$4<=TODAY() didn't.

Hi

try

=$I4<=TODAY() applies to =$I$4:$I$66

 

[MARZIOTULLIO]

Hi

[h=1]Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation[/h]
https://www.youtube.com/watch?v=_PBPU_5jTUA

 

[Gerald Higgins]

Yes the $ sign will have been the problem, I should have spotted it earlier.

Think about it this way.
With
=$I$4<=TODAY(), the CF on every cell evaluates whether I4<=TODAY, and it either is or it isn't, but every cell gets the same result because every cell is looking at I4.

With
=$I4<=TODAY(), the CF on each cell evaluates whether the cell in column I on the current row is <=TODAY.
So on row 5, it evaluates I5.
On row 6 it evaluates I6.
And so on.