Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Combine Multiple Conditional Formatting Rules

    I have a question for the community regarding combining conditional formatting rules.

    Currently, I have 3-rules with the same formatting. They are:

    =AND($A2<>””,AND($B2<>0))

    =AND($C2<>””,AND($D2<>0))
    =AND($E2<>””,AND($F2<>0))

    The rules are applied to rows 2-12 in columns B, D and F.

    Is it possible to have a single Conditional Formatting rule that would check columns A, C and E (rows 2-12), if they are NOT BLANK, then check the values in B, D and F, if one or more ARE NOT equal to 0, apply the formatting to that particular cell?

    For example, if:

    A2 is blank and B2 = 0 (no formatting applied to B2), C2 is NOT blank and D2 = 1 (formatting is applied to D2) and E2 is NOT blank and F2 = 0 (no formatting applied to F2). If the value of D2 is changed to 0, the formatting would disappear and if the values of B2 and/or F2 were changed to 1, the formatting would apply to those cells.

    I’ve tried to combine the 3-rules into a single Conditional Formatting rule using various combinations of AND and OR statements. I’m not getting any error messaged, I am just not getting the desired results.

    =AND(A2<>"",AND($B2<>0),OR(AND(C2<>"",AND($D2<>0,O R(AND(E2<>"",AND($F2<>0)))))))

    Any help is greatly appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JAATR,

    Is this what you want?
    JAATRCondFmt.JPG

    The key is the range it is applied to:
    =$B$1:$B$9,$D$1:$D$9,$F$1:$F$9

    JAATRCondFmtRng.JPG

    Note: You do this by highlighting the ranges above before entering the conditional formatting menus. Note: the first time I did it Excel changed the references in my formula! I had to go back in and edit the formula then it worked as expected. YMMV!

    HTH
    Last edited by RetiredGeek; 2016-08-25 at 13:27.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JAATR (2016-08-25)

  4. #3
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    Thank you, that’s exactly what I needed. Although, to be honest I will need to study this a little to make sure I completely grasp the concept.

    After I posted my message, my layout changed a little. I have inserted 5 new columns (between D and E). None of the new columns would not have any conditional formatting, so I not sure what impact this would have on the solution you provided. It’s going to be later this evening before I can test it using my new layout. I will make sure I to take a close look at the new ranges that I apply the Conditional Formatting too.

    Again, thank you for your help.

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi JAATR

    As you can see from RG's post, RG had to create a test workbook to demonstrate the solution.
    Now, if you could attach a sample file for your new layout, it would then be easier for helpers to work with, and for a suggested solution to be posted back.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    JAATR (2016-08-26)

  7. #5
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Updated Layout

    RetiredGeek / zeddy,


    I've attached the workbook. I've also included a screen shot. I'm sorry for the changes, I'm trying to help a family member and they think since I can turn on a computer, I'm an expert.....at this point, I think there're set with their layout/format.

    Basically, I have columns A & B and F & G applying the correct conditional formatting based on RetireedGeek's solution. It may not be possible, but I would also like to include columns J & M into the same conditional formatting rule without creating a second rule.


    CF.jpg
    Attached Files Attached Files

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JAATR,

    You will have to create a second rule for the J:M pairing because they are not adjacent like A:B & F:G.

    The same formula logic can be used just select column M then when your create the formula reference column J instead of A. The rest stays the same.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JAATR (2016-08-26)

  10. #7
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I had a feeling that would be the case. I really appreciate you taking the time and effort to help me.

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    JAATR,

    On the contrary, this can be done with one formula for the conditional formatting of Columns A-B, F-G, and J-M

    Code:
    The formula Rule is :
    =IF(COLUMN()=2,AND($A3<>"",$B3<>0),IF(COLUMN()=7,AND($F3<>"",$G3<>0),IF(COLUMN()=13,AND($J3<>"",$M3<>0))))
    
    And the Applied to range remains unchanged:
    =$B$3:$B$8,$G$3:$G$8,$M$3:$M$8
    HTH,
    Maud

    JAATR.png
    Attached Files Attached Files
    Last edited by Maudibe; 2016-08-26 at 18:37.

  12. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    JAATR (2016-08-26),RetiredGeek (2016-08-26)

  13. #9
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Maudibe,

    Awesome; I cannot thank you enough. I don’t do a lot with Excel; but I know if I ever need help, someone has the answer(s). This is such a great community and I really appreciate the support I always receive.

  14. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Thanks but I saw RG do something like this a while back and its been sitting in my bag of tricks ever since!

    The formula could be simplified to:

    Code:
    =IF(COLUMN()=13,AND($J3<>"",$M3<>0),AND(A3<>"",B3<>0))
    Last edited by Maudibe; 2016-08-27 at 00:23.

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
  •