Conditional formatting rules, multiple conditions to check

[TomPeregrinNZ]

Hi - we have different types of milk that we test for acidity. High acidity is a problem, so I want conditional formatting to highlight these results. However, sheep milk naturally has higher acidity that other milks so has a different limit. Sheep milk limit is max 0.24, all other milks are max 0.18.

Furthermore we get sheep milk from different customers. I have created a column that checks for "sheep" in the text of the product description and returns "Yes" if true. This future-proofs the system so if we get another sheep milk customer the formatting will still work.

I have created a series of 4 conditional formatting rules that I want to apply in order:
=J6>0.18, cell fill is orange
=P6="No", no format set and stop if true
=P6="Yes", change cell fill back to white (sheep results from 0.19-0.24 show as normal)
=J6>0.24, change font to red bold.

However the series of formulas seems to be only doing the first rule and not progressing. What am I doing wrong?

[nick.williams]

Try a single rule with the formula:

=J6>IF(P6="Yes",0.24,0.18)

Edit - noticed you had different formats for sheep and non-sheep. In which case use two formulae:

=AND(P6="Yes",J6>0.24)
=AND(P6="No",J6>0.18)

I've had trouble with conditional formatting apparently "stopping" when it is not supposed to, as well. But I think later rules don't override earlier ones if they apply to the same text. So I guess it colours orange if >0.18, and then otherwise has no format.

[TomPeregrinNZ]

Awesome, that's perfect thankyou

[nick.williams]

Glad it helped.

Personally the red text doesn't stand out to me like highlighting does, so I'd probably highlight it a different colour (or orange with red text), but obviously up to you.