Consulting

Results 1 to 15 of 15

Thread: Conditional Formatting Help

  1. #1

    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.

    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."
    Last edited by distillerjun; 03-22-2016 at 12:39 PM. Reason: add text

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3

    *sigh*

    yep. Still, the same result....

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Then supply a file to experiment with.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5

    File attachement

    Thanks!

    HIT_2016.xlsm

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Protected sheet.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7

    I'm sorry.



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

    HIT_2016.xlsm
    Last edited by distillerjun; 03-23-2016 at 06:54 AM. Reason: two oops's

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Simplified conditional formatting
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    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?

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by distillerjun View Post
    So, you left the formula the same
    Yes.




    Quote Originally Posted by distillerjun View Post
    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 View Post
    Will this expand as I add more rows?
    Yes.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11

    table formatting

    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.

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    It works here when I insert rows (no special 'format same as below).
    What version of Excel are you using?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13

    Formatting

    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!

    HIT_2016.xlsm

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Protected sheet.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •