Forum Discussion
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?
I'd use two rules instead of the four you have. And you probably want to apply them to $J$6:$J$100 or so, instead of just to $J$6.
Leave the rule for J6>0.18 alone, and delete the other three.
Click 'New rule...'
Select 'Use a format to determine which cells to format'.
Enter the formula =AND(P6="Yes",J6>0.24).
Click Format...
Activate the Font tab.
Select red as font color.
OK your way out.
2 Replies
I'd use two rules instead of the four you have. And you probably want to apply them to $J$6:$J$100 or so, instead of just to $J$6.
Leave the rule for J6>0.18 alone, and delete the other three.
Click 'New rule...'
Select 'Use a format to determine which cells to format'.
Enter the formula =AND(P6="Yes",J6>0.24).
Click Format...
Activate the Font tab.
Select red as font color.
OK your way out.
HansVogelaar Awesome thanks. I got a reply on another Excel forum a few minutes before you. I ended up doing this which is essentially the same as you have said, and it works great.
