Conditional Formatting Help

[distillerjun]

I need a Conditional Formatting formula to enter in "Use a formula to determine which cells to format" that looks at dates in Column B and highlights them if they are 90 days or more old if column F text is either "Investigating" or "Implementing."

This is the formula I currently have: = and($B2<=(today()-90),or($F2="investigating",$F2="implementing"))

It works to an extent but it highlights extra dates that are less than 90 days old and I am unsure why. :banghead:

As an alternative, I am using the Conditional formatting: Format only cells that contain: Cell value - less than or equal to - =NOW()-90 because that is the ONLY formula I can get to work correctly on this date but it formats all cells that are 90 days or over old not just the ones based on column F that contain only "investigating" or "implementing."

[p45cal]

That all seems all right but did you make sre that B2 (or any cell on row 2) was the active cell while you were applying the conditional formatting?

[distillerjun]

yep. Still, the same result.... :(

[p45cal]

Then supply a file to experiment with.

[distillerjun]

Thanks!

15737

[p45cal]

Protected sheet.

[distillerjun]

:oops:

I really thought I took the protections off before I uploaded it. Sorry about that.

15740

[p45cal]

Simplified conditional formatting

[distillerjun]

So, you left the formula the same just changed the selection to only include implementing and investigating? Will this expand as I add more rows?

[p45cal]

So, you left the formula the sameYes.





just changed the selection to only include implementing and investigating? ??
No. The CF applies to the whole of column B within the table, no exclusions.
As I suggested earlier. you will have had B1 active when you applied the CF, not B2;that's why some cells were incorrectly highlighted eg. one cell (B30) was being highlighted because of this. This was further complicated by the same format being applied with other conditions:
= AND($B36<=(TODAY()-90),OR($F36="investigating",$F36="implementing")) was being applied to B34 alone (2 rows out of kilter, so here you were highlighting B34 according to what was happening 2 rows below)
= AND($B24<=(TODAY()-90),OR($F24="investigating",$F24="implementing")) was being applied to B52 alone (28 rows out of kilter)
= AND($B52<=(TODAY()-90),OR($F52="investigating",$F52="implementing")) was being applied to B53 alone (1 row out of kilter)
There were 7 of these, all deleted now.
You also doubled up on column F's CF which I consolidates into single CFs.

For column B, I kept one formula:
= AND($B2<=(TODAY()-90),OR($F2="investigating",$F2="implementing"))
and applied it to $B$2:$B$208, the whole of column B within the table.

For column F I did something similar.






Will this expand as I add more rows?Yes.

[distillerjun]

I did not add all that conditional formatting. I don't know why all the extras were applied. It didn't show up in my conditional formatting list, I would have deleted them.

Currently, I am having an issue trying to get the formatting to expand to new rows I create at the top of the table now. I had it formatted correctly but the new conditional fomatting you applied must have somehow erased that. I need it to apply the conditional formatting to the new rows created at the top of the table. I tried Insert Row and then clicking format same as below but it doesn't follow to all new rows, I have to do it every time.

[p45cal]

It works here when I insert rows (no special 'format same as below).
What version of Excel are you using?

[distillerjun]

Microsoft Office Professional Plus 2010

Like I said, I had it formatted previously but when the new conditional formatting was applied, something happened to the table formatting. I attached it so you can see what I am looking at.

Thanks!

15743

[p45cal]

Protected sheet.

[distillerjun]

Nevermind! I figured it out! Thanks for all your amazing help! No one has been able to help me in the last 4 months with this! Thank you so much!