Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2016
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Help with a Custom Formula Conditional Formatting Rule

    I am a new poster, but I search this forum regularly for answers (examples) to some basic Excel functions. I've used Excel's build-in Conditional Formatting (CF) rules in the past to apply some very simple formatting to cells; however, this is my first attempt to create a custom formula CF rule. I thought it would be fairly simple, but I've discovered it's a little more complicated than I anticipated. I tried various combinations of my formula; but I can't seem to get the desired results. At this point, I'm not sure if CF is capable of doing this.

    The rule would check two conditions: (1) check the date in a given cell to see if it's greater than or equal to today (2) check to see if a given cell is blank. If both conditions are TRUE, apply the CF to three cells.

    Example 1:
    (1) check the dates in cell B1; if the date is greater than or equal to today AND
    (2) check to see if B2 is blank, if TRUE apply the conditional formatting cells B1:B3.
    In this example, since both conditions are TRUE (Today()>=B1 and B2 is blank), the CF rule would be applied to cell B1 thru B3.

    Example 2:
    (1) check the dates in cell B4; if the date is greater than or equal to today AND
    (2) check to see if B5 is blank, if TRUE apply the conditional formatting cells B1:B3.
    In this example, condition one is TRUE, but B5 is FALSE so the CF rule would not be applied to cell B4 thru B6.

    Note: I don't think this would have an impact on my desired results, but the values in rows 2, 5 and 8 are limited to a 1 or -1.

    In my screenshot below, the rule would apply to cells B1:B3 and D46 because the dates in B1 and D4 are greater than today and B2 and D5 are empty.

    I would appreciate your help letting me know what I'm doing wrong.

    CF1.jpg

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Welcome to the Lounge as a new poster!

    Now, it's important to get your request straight:
    Re: "(1) check the dates in cell B1; if the date is greater than or equal to today AND"
    ..I think you meant
    "(1) check the dates in cell B1; if the cell date is earlier than today AND"

    So, my posted file shows you one method of doing this.
    The conditional format rule is just
    =H1
    ..and is applied to block [B1:E9]

    My example uses check columns (which could be hidden) to the right of your data.
    I'm sure you'll get some other methods, but you have to admit, my proposed Conditional Format formula is as simple as you can get!

    zeddy
    Attached Files Attached Files

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

    rtommie84 (2016-09-12)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    rtommie,

    I achieved this in a different way.

    If you take the same formula but apply it differently, it will work:

    Rule:
    =AND(B1<TODAY(),B2="")
    Applies to:
    =$B$1:$E$1,$B$4:$E$4,$B$7:$E$7

    Rule:
    =AND(B1<TODAY(),B2="")
    Applies to:
    =$B$2:$E$2,$B$5:$E$5,$B$8:$E$8

    Rule:
    =AND(B1<TODAY(),B2="")
    Applies to:
    =$B$3:$E$3,$B$6:$E$6,$B$9:$E$9

    This will also enable you to format the date rows bold red while the others remain normal black

    HTH,
    Maud
    rtommie1.png
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    rtommie84 (2016-09-12)

  6. #4
    New Lounger
    Join Date
    Sep 2016
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    zeddy and Maud,

    Thanks for your help. Both solutions work. I don't do much in Excel, but I learned a lot about conditional formatting playing around with the different approaches each of you took to my questions. Again, thank you for your help.

Posting Permissions

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