Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel - Multiple Conditional formatting to groups of cells within same sheet

    Hello! I have a "calendar" of inventory & I'm trying to apply conditional formatting to show if it is not here (pink), here (green) or installed (no color). My formula should show that if:

    IF column name "LEFT" >0 then "Item" through "Left" should be pink

    IF column name "HERE" =blank or >0 then "Item" through "Left" should be green

    IF column name "LEFT" =0 then "Item" through "Left" should be no fill/white

    I've tried a few things but all it does is color the entire row, not just the "Item" through "Left" columns.

    ScreenShot003.jpg


    COND FORM.xls

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    What happens if there is a conflict? Left > 0 and Here > 0 ... for example.

    Is the order left > 0, then here > 0 or blank, then left = 0?

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    What happens if there is a conflict? Left > 0 and Here > 0 ... for example.

    Is the order left > 0, then here > 0 or blank, then left = 0?
    Good question.

    Yes, the order is left > 0, then here > 0 or blank, then left = 0

    To give it context, I get a contract in to install, say, 9 items. Therefore, TOTAL is 9 & LEFT is, by default, 9 (row from "Item" to "Left" is pink at this point). Then 9 items are delivered for install so all 3 columns will say 9. As they get installed, the LEFT column will slowly dwindle down to 0 & turn green.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Post #1 IF column name "LEFT" =0 then "Item" through "Left" should be no fill/white
    Post #3: As they get installed, the LEFT column will slowly dwindle down to 0 & turn green.
    wsammy,

    your context description in post #3 does not match your images nor description in post #1

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Sammy,

    You also do not specify what color should be used if you only get a partial delivery, e.g. 9 ordered, 4 delivered.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    With apologies to Yogi Berra, Excel is 90 percent mental. The other half is physical.
    So here's my physical solution:

    1. Use a formula in the "LEFT" column (this means you only need to enter the Item Total due, and what is "HERE"
    2. Conditional formats have been applied separately to each Week column group.
    3. If you get less than was expected, the record remains pink. (i.e. short delivery)
    4. If you get exactly what was expected, the record is pale green
    5. If you get more than was expected, the record is darker green.

    (The attached file is .xls XL2003 format)

    zeddy
    Attached Files Attached Files

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

    WSammy (2016-04-15)

  8. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Zeddy - I see you came to a fork in the road and took it!

    My favorite Yogiism is about him going into a pizza shop and ordering a whole pie. The waiter asks should the pie be cut into 6 slices or 8 slices. Yogi asks that the pie be cut into 8 slices because he's really hungry. Something to remember the next time you're with friends ordering a pie.

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

    zeddy (2016-04-15)

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Fred

    ..I watched a baseball game once.
    I couldn't understand why the ball was getting bigger and bigger and bigger.
    Then it hit me.

    zeddy

  11. #9
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    With apologies to Yogi Berra, Excel is 90 percent mental. The other half is physical.
    So here's my physical solution:

    1. Use a formula in the "LEFT" column (this means you only need to enter the Item Total due, and what is "HERE"
    2. Conditional formats have been applied separately to each Week column group.
    3. If you get less than was expected, the record remains pink. (i.e. short delivery)
    4. If you get exactly what was expected, the record is pale green
    5. If you get more than was expected, the record is darker green.

    (The attached file is .xls XL2003 format)

    zeddy
    THANK YOU. I have a program at work that I accurately do this in. This excel calendar thing is only used for a quick reference in meetings & obviously is not 100% accurate. You have made my life a lot easier. Thank you!!!!!!

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Sammy

    Thanks for the feedback.
    Always good to know our efforts are appreciated. It makes a difference!

    zeddy

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Fred

    ..next time I have a pizza for myself, I'll just cut it in two pieces then.
    Mustn't appear greedy.

    zeddy

Posting Permissions

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