Conditional Formatting Help

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."

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?

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

Then supply a file to experiment with.

Thanks!

Attachment 15737

Protected sheet.

:oops:

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

Attachment 15740

Simplified conditional formatting

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?

Quote:

---

Originally Posted by distillerjun

So, you left the formula the same

---

Yes.

Quote:

---

Originally Posted by distillerjun

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.

Quote:

---

Originally Posted by distillerjun

Will this expand as I add more rows?

---

Yes.

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.

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

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!

Attachment 15743

Protected sheet.

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!