CF Stop If True not working correctly

[Ethan Strauss]

Hi,
I have a problem with Stop If True for Conditional Formatting not working
correctly in Excel 2007. It seems as though Stop If True is always on even
when the check box is not checked.
I have a very simple demonstration, at least on my machine.
Open a new workbook.
Enter the values 1, 2, 3 in cells A1, A2, and A3 respectively.
Select those three cells.
Select New Rule from the Conditional Formatting menu
Create a rule of "Format only cells that contain"
Make the rule as follows:
Cell Value
Is greater than 1
Fill is (select a color. I used red)
Cells A2 and A3 are colored red. This is correct and fine.
Make new rule as follows:
Cell Value
Is greater than 0.
Fill is (select a different color. I used green)
All cells are now green!.
Check Manage Rules to see the order and status
The Cell Value > 0 rule comes first. Stop If True is NOT checked. So,
only cell A1 should be green. The others should be red!
If I reverse the order of the rules, so CellValue >1 is first, Cells A2
and A3 turn red. This should not be the case unless "stop If True" is
checked, which it is not.
It appears to me like it always Stops If True and it just ignores the
checkbox.
Any idea what is wrong or how to fix it?
I am using Excel 2007 with SP1 running on Office XP professional SP3.
Note that this is a simple example designed to demonstrate the problem. I
have seen this in various more complex situations.

Thanks!
Ethan

 

[Shane Devenshire]

Hi,

It is working correctly, you just need to think like Excel. In the first
case rule 1 changes all the cells to green, it does not matter whether Stop
if True is on or off in this case. Since both solid fills can't occur at the
same time the first one is applied. However, suppose you add the condition
Font = Bold to your second condition (the one that makes the cells red) when
you apply this set of rules with Stop if True off all the cells turn green
AND the second two cells also become Bold. Then if you turn Stop if True on
all the cells turn green but non of the cells become bold.

Think on this awhile and I think it will become clear. If you want the
second two cell to turn red you would apply the conditions in the opposite
order, and then again Stop if true would not be a factor. So stop if true is
really about applying more than one format at the same time to a range,
something we could not do in 2003.

 

[Ethan Strauss]

Hmm.
Can this be summarized as follows:
If multiple conditional formatting rules which effect the same aspect of
formatting (fill color, boldness, font size, etc) exist, the first rule
always takes precedence. The "Stop If True" checkbox being unchecked allows
multiple rules which affect different aspects of the format to co-apply to
the same cell.
Thanks!
Ethan

 

[Ron Rosenfeld]

It appears to me like it always Stops If True and it just ignores the
checkbox.

My understanding is that the Stops If True box is to help simulate the behavior
of earlier versions of Excel which were limited to just three CF rules.

Any idea what is wrong or how to fix it?

If you want more than one rule to apply, they must be non-conflicting rules. In
your example, more than one rule could apply to the same value, so only that
rule with higher precedence would apply. You obtain your desired result by
ordering your rules in your desired precedence order.
--ron