I hadn't tested the "Stop if True" check box and assumed it operated as it suggests by its existence. Unfortunately, CF seems to ignore the check and treats all lines as if all the boxes are checked.
Yes, conditional formatting is very confusing indeed. When I had a look at this I thought it wasn't working as it should and
Stop if True seemed a bit random!
So looking into it a bit deeper I found where I was going wrong.
The first thing I noted was that the first of the conditional formats that was TRUE was king and even if conditional formatting lower down the list was also TRUE, that lower down CF did not apply
if the formatting was in conflict with the first that was true.
It's that last underlined bit which is important.
When you go into setting the formatting from the conditional formatting dialogue box, there are several aspects of formatting which you can change. Importantly, there are aspects which are not 'set' in a given CF. Those 'unset' aspects do not change the formatting that was there before.
Here's a pic:
You can see above that one cell fulfils the 2nd CF and both cells fulfil the first, but only the top CF is in action, because the
aspect that each CF is trying to affect is the fill colour of the cell. (An easy way to rectify this in this case is to reverse the order of CFs.)
To show you that the second CF is actually in operation I'll set some more aspects of formatting for the 2nd CF (bold italic and strike-through):
Where you can see that those aspects have been applied to cell D4 only, but that the brown of the first CF remains and the red doesn't show at all.
When setting the CF you get a
Clear button. This what you see before clicking it:
and after clicking it:
So if a true CF has an aspect of formatting that is in conflict with another true one higher up it doesn't show; if it's not in conflict it will show.
Finally,
Stop if True works as expected, here you can see that the 2nd CF hasn't been applied at all:
So while not giving you the actual CFs you want (there are several questions that I'd need answering before doing that) I hope I've equipped you with what's necessary to stop you going insane!
(I've not come across this sort of stuff on the interweb much at all; most of this is through trial and error.)
continued in next message; can only add 5 pictures…
