Conditional formatting rules, multiple conditions to check
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?
Last edited by TomPeregrinNZ; 03-10-2022 at 05:49 PM.
Re: Conditional formatting rules, multiple conditions to check
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.
Last edited by nick.williams; 03-10-2022 at 05:55 PM.
Re: Conditional formatting rules, multiple conditions to check
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.
Bookmarks