Share via

confused in the working of Stop If True feature of Conditional Formatting

Anonymous
2012-08-02T20:21:11+00:00

I sure find myself to be confused in the working of Stop If True feature of Conditional Formatting. Consider the following example:

  1. I have selected the region A1:D10 which is filled with formula =RANDBETWEEN(1,100)
  2. I insert the first rule as a formula =A1>0 (To be filled with Green)
  3. Second rule's formula is =A1>33 (To be filled with Yellow)
  4. Third and final rule is =A1>67 (To be filled with Red)

(therefore the order/sequence of the rules is first Red one then the yellow one and finally the green one)

Now if I check the Stop if True in front of the very first rule I think the rule, upon finding numbers in the region which are greater than 67 be filled with RED and stop working, similarly if Stop if True checkbox in front of the second rule is also checked I think it should kick start and the numbers greater than 33 should also be colored YELLOW and work should stop, i.e. leaving the final rule.

But that doesn't seem to work and all rules are working.

Any logic/explanation/clarification with a correct example would be appreciated how to have the above strategy be applied?

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-08-02T21:13:47+00:00

    Faraz,

    I'm not sure if there's some kind of colour (or other) precedence...

    Consider another example, just using cell B2, and with these CF formulas & formats:

    1. =B2>67   blue fill, black font    Stop if True
    2. =B2>33  yellow fill, white font    Stop if True
    3. =B2>0    black continuous line border, black font

    Now enter 68 in cell B2 and I see blue fill, black font, no border.

    Now remove the Stop if True from rule 1 and I see blue fill, white font, no border.

    Now remove Stop if True from rule 2 and I see blue fill, white font, and black continuous line cell border.

    *** EDIT ***

    Actually, checking with the CF Help page, showed as I guessed:

    What happens when more than one conditional formatting rule evaluates to true

    For a range of cells, you can have more than one conditional formatting rule that evaluates to true. Either the rules don't conflict or they conflict:

    When rules don't conflict   For example, if one rule formats a cell with a bold font and another rule formats the same cell with a red color, the cell is formatted with both a bold font and a red color. Because there is no conflict between the two formats, both rules are applied.

    When rules conflict   For example, one rule sets a cell font color to red and another rule sets a cell font color to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence.

    ___________

    Regards, Tom

    10+ people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-08-02T21:42:26+00:00

    Excellent example Tom!

    Thanx

    2 people found this answer helpful.
    0 comments