PDA

View Full Version : [SOLVED] Conditional Formatting Help



distillerjun
03-22-2016, 12:36 PM
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
03-22-2016, 01:25 PM
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
03-22-2016, 01:52 PM
yep. Still, the same result.... :(

p45cal
03-22-2016, 02:05 PM
Then supply a file to experiment with.

distillerjun
03-22-2016, 02:22 PM
Thanks!

15737

p45cal
03-22-2016, 05:28 PM
Protected sheet.

distillerjun
03-23-2016, 06:53 AM
:oops:

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

15740

p45cal
03-23-2016, 07:51 AM
Simplified conditional formatting

distillerjun
03-23-2016, 08:41 AM
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
03-23-2016, 11:36 AM
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
03-23-2016, 11:47 AM
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
03-23-2016, 11:58 AM
It works here when I insert rows (no special 'format same as below).
What version of Excel are you using?

distillerjun
03-23-2016, 12:09 PM
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
03-23-2016, 12:42 PM
Protected sheet.

distillerjun
03-23-2016, 01:57 PM
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!