Trouble With Conditional Formatting, Rows, and In-Betweens

SpookyNoodle

New member
Joined
Oct 16, 2017
Messages
1
Reaction score
0
Points
0
So, before we get too far into this, I should say: Yes, I have done searching for this, and no, I haven't found any tutorials, guides, or tips that are helping me sort this out.

I'm working on a spreadsheet (obviously), and I want to conditionally format the entire row based on the data found in one column at the end of the row. Obviously, I had to do custom rules and formulas to achieve this, which I found out after some searching.

The problem is this: nothing is working the way it's supposed to (as far as I can tell).


oyVSx4i.png



The rules I'm trying to achieve are this:

If the H value is between 0 and 1600, I'd like the entire row to be green.

If the H value is over 1600, I'd like the entire row to be yellow.

If the H value is below 0, I'd like the entire row to be red.

Problem is, no order I've tried as made this work, and the only thing is a multi-colored mess.

What am I doing wrong or missing?
 
Order the rules from high (>1600) to low, and then tick the stop if true box next to each.
 
Capture3.JPG
The confusing bit about conditional formatting is where it says "Rule (applied in the order shown)" which implies the rules are followed top down.
In fact, as is the case here, 2 conditions can be true at the same time (values greater then 1600 are also greater than 0). If the formats don't conflict (eg. one makes bold text, the other colours it red) both are applied.
In this case they DO conflict (both try to colour the background of the cells differently), so Excel interprets this is by precedence; the higher rule/condition takes precedence over a lower one.
The tick boxes are there for compatibility with Excel 2003 and earlier (there were no tick boxes then) where I think the rules were applied top-bottom and did stop at the first TRUE condition.

One more thing, when applying the conditional formatting make sure that before you go into the dialogue box, that you select all the cells that you want the formatting applied to and that the active cell is in the row that the formula you're using looks at. In this case =$H2>1600, so make sure the active cell is in row 2.
 
Last edited:
Back
Top