Conditional Formatting That Indicates Multiple Conditions

[jbillh]

Hi Folks,

We're using Excel 2016 on Win 10 pc's.

We use conditional formatting to change the color of a cell (to pink) if the number in it is <= to 100. That is working just fine and tells us our inventory levels are too low and we must reorder now.

What we'd like to do, however, is keep that condition in place for that cell and add another condition that "if the number(s) in A2, or A3 or A4 are >0 we then want to change the color to green.

This syntax is completely wrong but this might convey the idea:

Condition #1:
If A1<=100 Re-color this cell to Pink

Condition #2
If A2 or A3 or A4 have a number >0 Re-color A1 to Green

In practical (real world) terms, the sum in A1 represents our "Inventory In-Stock" which may be zero or any other number. If there is a number (other than 0) in A2, A3 or A4 however, those number(s) represent pending orders for more inventory. A green color in the A1 cell will tell us not to worry about our low inventory levels... we already ordered more.

So... Pink means we need to order more inventory (because there are no numbers >0 in A2, A3 or A4) and Green means we already ordered more inventory (because there are number(s) >0 in any of A2, A3 or A4.

This has kicked our butts all day long. Any advice would be much appreciated!

Thanks Much,

Bill

 

[Noodleski]

Could A2, A3 or A4 be negative? I assume they can't.

So for conditional format, select formula and enter following:
=sum($A2:$A4)>0 (set conditional format to green)

And don't forget to switch the priority so that the new condition has the highest priority.

 

[jbillh]

Could A2, A3 or A4 be negative? I assume they can't.

So for conditional format, select formula and enter following:
=sum($A2:$A4)>0 (set conditional format to green)

And don't forget to switch the priority so that the new condition has the highest priority.

Thanks Noodleski,

I'm a little lost on your reply. Probably because A1 already uses a formula to get it's number. The syntax "=T70" is what gives A1 it's number. Not sure how to proceed.

Thanks Much,

Bill

P.S. No negative numbers... thanks for asking.

 

[Noodleski]

Ok, we'll get there.
When you click conditional format, you can do more than take the standard solutions (greater than, smaller than, equal to)
Choose conditional format => new rule => use formula to determine which cells to format. (description can differ as I use 2013)

There, fill in the formula I gave you in the previous reply.

 

[jbillh]

Ok, we'll get there.
When you click conditional format, you can do more than take the standard solutions (greater than, smaller than, equal to)
Choose conditional format => new rule => use formula to determine which cells to format. (description can differ as I use 2013)

There, fill in the formula I gave you in the previous reply.

You Are Good!

Thanks so much! I put in the real cell refs and it worked like a charm.

This is what I entered: "=sum($C51+$C74+$C98)>0"

Not sure if there is better syntax for this but I'm very happy with the result and truly appreciate your help mate!

All the Best to You and Yours,

Bill

 

[Noodleski]

Glad to hear you're pleased with the result.

 

[jbillh]

Glad to hear you're pleased with the result.

Very Thankful Mate. I wish there were some way to repay you.

Take Good Care,

Bill