locked
Conditional Formatting Formula RRS feed

  • Question

  • What I'm looking to do is, if a date had been entered into Col I, then I want the corresponding cell in Col H to have no conditional formatting (or a white cell fill).

    The only cells in Col H to appear as red, are the ones in the past who haven't yet got an entry in Col I.

    Thursday, July 23, 2020 12:05 PM

Answers

  • Select the range in column H that you want to format. I will assume that H2 is the active cell within the selection.

    If necessary, set the Fill Color to No Fill, and delete existing conditional formatting rules.

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =AND(H2<TODAY(),I2="")

    Click Format...

    Activate the Fill tab.

    Select red.

    Click OK, then click OK again.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by ChickLeg Monday, July 27, 2020 1:23 PM
    Thursday, July 23, 2020 12:25 PM

All replies

  • Select the range in column H that you want to format. I will assume that H2 is the active cell within the selection.

    If necessary, set the Fill Color to No Fill, and delete existing conditional formatting rules.

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =AND(H2<TODAY(),I2="")

    Click Format...

    Activate the Fill tab.

    Select red.

    Click OK, then click OK again.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by ChickLeg Monday, July 27, 2020 1:23 PM
    Thursday, July 23, 2020 12:25 PM
  • Sorry, I should have said I have other conditional formatting rules in Col H.

    Orange if date between =today() and =today()+30

    Yellow if date between =today() and =today()+90

    Red if date is less than =today()

    The yellow and orange CF needs to remain.

    Thursday, July 23, 2020 12:44 PM
  • How about changing the 3rd rule (red if date is less than today) to the one I described?

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, July 23, 2020 2:56 PM
  • Hi ChickLeg,

    You can refer the formula below(in my tests, i use column A and column B):

    For a better display, I set the conditional format to green (for example, if cell B2 is not empty, then cell A2 fills green), and in your case, you should change the green to white.

    =$B2<>""

    At the same time, in my tests, I have other conditional formats that don't affect each other and remain.

    This “Excel IT Pro Discussions” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

    Best regards

    Itch Sun



    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !
    Friday, July 24, 2020 9:43 AM
  • Worked a treat. Many thanks.
    Monday, July 27, 2020 1:23 PM