Conditional Formatting

[seasmith]

Good afternoon everybody! I'm having a little conditional formatting issue that I can't seem to figure out. I have Inspection Date in Column M and Interval in Column N. I need to conditionally format column M to turn red when the

Inspection Date <= Todays Date - 365*(1*Interval)

Is there anyway to do this for all the cells down to row 385 without having to condionally format each cell individually?

 

[tc88]

Good afternoon everybody! I'm having a little conditional formatting issue that I can't seem to figure out. I have Inspection Date in Column M and Interval in Column N. I need to conditionally format column M to turn red when the

Inspection Date <= Todays Date - 365*(1*Interval)

Is there anyway to do this for all the cells down to row 385 without having to condionally format each cell individually?

Before you select "Conditional Formatting", highlight all of your cells. That should apply the conditional formatting rule to all 385 cells.

 

[seasmith]

Thanks I did that, but now how do I put the interval column in there?

like I want:


Inspection Date in Row 1 <= Todays Date - 365 * (1*Interval in Row 1)
Inspection Date in Row 2 <= Todays Date - 365 * (1*Interval in Row 2)
.
.
.
Inspection Date in Row 385 <= Todays Date - 365 * (1*Interval in Row 385)

 

[Alphacsulb]

Change Interval in Row 385 to the cell like:
= TODAY()-365*(1*N385)

 

[seasmith]

Oh yeah that would make sense. But do I have to do that for every cell individually from N1-N385? Or is there a way to put a range into conditional formatting? So I could just highlight all the cells and do one conditional format instead of 385 different ones?

 

[tc88]

Oh yeah that would make sense. But do I have to do that for every cell individually from N1-N385? Or is there a way to put a range into conditional formatting? So I could just highlight all the cells and do one conditional format instead of 385 different ones?

As I said above, I believe if you highlight all of the 385 cells before applying the conditional formatting rules, it will apply the rule to all selected cells.

 

[seasmith]

Oh yes, I'm sorry I misunderstood your first comment. For some reason it doesn't work for all of them, my conditional formatting is:

Cell Value <= =TODAY()-365*(1*$N$7:$N$385)

if the cell value is less than that it turns the cell red but for some reason it doesn't work for all. For example, in M301 the date is July 10, 2010 and the interval in N301 is 1 so it would be

July 10, 2010 <= July 19, 2011 - 365(1*1)

July 10, 2010 <= July 19, 2010 and the cell isn't red. So that isn't right

 

[Marcelo Branco]

Maybe this


Select M1:M385
CF > Clear rules

CF > New Rule > Use a formula to...

and insert this formula
=M1<=TODAY()-365*N1

HTH

M.

 

[seasmith]

That way worked great! thanks so much I never thought of doing it like that

 

[Marcelo Branco]

That way worked great! thanks so much I never thought of doing it like that

You are welcome and tks for the feedback

M.