Results 1 to 9 of 9
  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

    Excel 2010 - Filling and formatting cells based on a date

    I have a simple need: to put values in two cells, based on the day of the week of a date entered into a third cell. All three cells need to be conditionally formatted depending on the entry in one of the cells and on the date entered.

    I've got it partially working, but am frustrated with the part that I can't get.

    Everything is explained in the attached very small workbook.
    Attached Files Attached Files
    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,

    This should do it.
    LouS-Colors.JPG

    Test File: LouS-Colors.xlsx

    BTW: Your Example has an incorrect value in Col A3!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    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 missed the Today rule in the first shot.
    LouS-Colors.JPG

    Test File: LouS-Colors.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Lou Sander (2015-04-28)

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Good stuff! Thanks a million. I will try to figure it out in the morning when my mind is working better.

    The error in A3 was because I couldn't figure out how to put "ERROR" into a cell. It was "PB" for Wed and Sat, and "MM" for anything else.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #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
    After action report:

    I see that the text box in my original sample sheet wasn't as large as it should have been, therefore cutting off several lines of text. Sheesh!

    I've implemented the new stuff in my main lottery workbook. It works great! The only glitch was that the fourth conditional format, to put the "ERROR" stuff in its own format, made my workbook incompatible with Excel 2003. It wasn't all that necessary, so I removed it. Now the workbook seems to be perfectly compatible with Excel 2003 and 2010.

    Further question:

    When I'm setting up conditional formats, I always have problems with the formulas, etc. I enter them in what I think is a rational way, and they end up with extra quotation marks. I then have to look closely at them and delete the extras. I haven't seen this covered in any Excel help or manuals. Maybe I am doing something wrong.

    Are there instructions for entering formulas in the Conditional Formatting dialog boxes?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You may also like this CHOOSE formula for col A
    '=CHOOSE(WEEKDAY(C3),"error","error","mm","Pb","er ror","mm","pb")
    and chg the CF to reflect =<today()
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  8. The Following User Says Thank You to Supershoe For This Useful Post:

    Lou Sander (2015-04-28)

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

    The extra quotation marks usually stem from forgetting the initial = sign. You must start every conditional format equation with an equal sign! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Lou Sander (2015-04-28)

  11. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, the light just went on! In my ignorance of the need for an equals sign, I've been fooling with those quotation marks for years.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    The result of all this help...

    The help you fellows have given on this little worksheet, plus other help I've gotten here in the past, have let to some pretty cool improvements in one of my "real" worksheets. Attached is a comparison of the new and the old.

    I couldn't have done it without you!
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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