Results 1 to 11 of 11
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Conditional Formatting (Excel 2010)

    This is driving me crazy.

    In the range D6:AD175, I want the cells in columns D through AD to be have a gray fill color if column C in the corresponding row is blank (it will either be blank or contain a manually entered date). Otherwise the fill color should be whatever fill color is set on the worksheet. Mostly this will be No Fill, but I need to be able to make it something else as required.

    As an example, if C120 is blank, I want D120 through AD120 to be formatted as gray. Otherwise they should be whatever color they are given by default.

    I've fooled with this a LOT, and never gotten it to work properly. The conditional formatting tends to be erratic, with some cells turning gray, and some remaining in their regularly formatted color. I have a feeling that my experimentation has left a lot of trash in the form of conflicting rules. I probably need to remove all conditional formatting in D6:AD175, then start over from scratch. Of course I don't want to do that until I'm sure what to put in the conditional formatting rule(s).
    Last edited by Lou Sander; 2015-06-23 at 19:30. Reason: clarify
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    1. Ok in the NameBox type: D6:AD175 {enter} - Selects the area for Conditional Formatting
    2. Select: Conditional Formatting from the ribbon.
    3. Select: New Rule
    4. Select: Use a formula to determine....
    5. In the formula box type: =C6=""
    6. Click: Format button.
    7. Select: the Fill Tab.
    8. Click: on the Grey value you want.
    9. Click: OK.
    10. Click: OK.
    11. Done.

    LouBefore.JPG
    LouAfter.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    After Step 2 and before Step 3, I am tempted to Clear Rules > Clear Rules from Selected Cells. That might clean up some trash. Whadda ya think?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Surely can't hurt! But you did say you wanted to preserve existing cell background colors in the OP?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I did remove all conditional formatting before re-doing it. The results are strange, per the image below. They are even stranger in other parts of the worksheet.

    Capture.PNG
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Fixed it!

    I put a dollar sign before the C. As in =$C6=""

    Everything seems to work perfectly now.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Sorry I forgot to type the $ I had it in my test file.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    For goodness sake! what is it with you guys and your dollars.

    zeddy

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

    You just had to POUND me for that didn't you!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    et tu Brute

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sterling work, chaps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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