Conditional Formating based on mutiple cells and multiple values

[Marky31mark]

Hello, I am hoping someone can help me. I am looking to do a conditional format on my cells, based on 3 criteria and multiple cells. See example below. If column M/Q or X are highlighted with a color I would like columns A-H to be highlighted in the same color. Values: 5 to 365 = "RED", 1 to 4 = "Orange", -1 to - 120 = "Green"

If someone could help I would really appreciate it.

Thanks,

 

[Mick Peters]

Hi mark31mark,
Sorry I can't see the image you have added but wanted to add a little help that might point you in the right direction. Conditional formatting formulas can only be true or false. So first work out a formula that give a true or false answer EG IF (and(M1>5,Q1.5,X1.5,True,False) then when you have that working you can add it to the conditional formatting. If you can add some more detail without compromising personal data then more people may be able to help with the exact formula.
I hope this helps
Mick

 

[Marky31mark]

Hey Mick,
Thanks for your reply. I seem to still be having an issue with this. Still trying to work on conditional formatting the "Number" column to correspond with my "expected ship vs Conf. Ship (Days)" column.

These columns are being pulled from another worksheet I have, so they are all formulas.

Here is my current formula: =IF(OR(AND($AM4="Received"),AND($M4>=5,$Q4>=5,$X4>=5,$AD4>=5,$AG4>=5)),"TRUE","FALSE"). Column AM I am using to determine if the ship date is conformed (using a "ISNUMBER" function). Again, these fields are being pulled from another worksheet.

Red = 5 to 365
Green = -1 to -365
Orange = 0

[TABLE="width: 1129"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Memo[/TD]
[TD]Deliv Date[/TD]
[TD]Amount[/TD]
[TD]Open Balance[/TD]
[TD]Expected Ship vs. Conf. Ship (Days)[/TD]
[TD]Expected Ship vs. Conf. Ship (Days)[/TD]
[TD]Expected Ship vs. Conf. Ship (Days)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1129"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]231121952 [/TD]
[TD]TOOLING[/TD]
[TD]5/7/2016[/TD]
[TD="align: right"]$730.00[/TD]
[TD="align: right"]$730.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231121985[/TD]
[TD]Stock[/TD]
[TD]6/3/2016[/TD]
[TD="align: right"]$11,735.00[/TD]
[TD="align: right"]$1,123.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]231121989[/TD]
[TD]Tooling[/TD]
[TD]6/24/2016[/TD]
[TD="align: right"]$4,000.00[/TD]
[TD="align: right"]$4,000.00[/TD]
[TD][/TD]
[TD]30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231221990[/TD]
[TD]STOCK: Charleston[/TD]
[TD]10/11/2016[/TD]
[TD="align: right"]$58,752.00[/TD]
[TD="align: right"]$58,752.00[/TD]
[TD][/TD]
[TD] -31[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231241991[/TD]
[TD]Hay PO 818405[/TD]
[TD]10/12/2016[/TD]
[TD="align: right"]$7,340.60[/TD]
[TD="align: right"]$7,340.60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]231421993[/TD]
[TD]Hay PO 818404[/TD]
[TD]10/12/2016[/TD]
[TD="align: right"]$9,081.00[/TD]
[TD="align: right"]$9,081.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]231271996[/TD]
[TD]Hay PO 884423[/TD]
[TD]10/12/2016[/TD]
[TD="align: right"]$12,078.00[/TD]
[TD="align: right"]$12,078.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]231221998[/TD]
[TD]Hay PO 899922[/TD]
[TD]10/12/2016[/TD]
[TD="align: right"]$10,182.00[/TD]
[TD="align: right"]$10,182.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]237122001[/TD]
[TD]TOOLING[/TD]
[TD]7/28/2016[/TD]
[TD="align: right"]$15,000.00[/TD]
[TD="align: right"]$15,000.00[/TD]
[TD][/TD]
[TD]30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231252002[/TD]
[TD]Buffer Stock[/TD]
[TD]7/29/2016[/TD]
[TD="align: right"]$39,850.00[/TD]
[TD="align: right"]$39,850.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]231922003[/TD]
[TD]STOCK[/TD]
[TD]9/1/2016[/TD]
[TD="align: right"]$8,400.00[/TD]
[TD="align: right"]$8,400.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]231722005[/TD]
[TD]TOOLING[/TD]
[TD]8/80/2016[/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]$500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231292007[/TD]
[TD]Margot Set PO[/TD]
[TD]10/24/2016[/TD]
[TD="align: right"]$19,680.60[/TD]
[TD="align: right"]$19,680.60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231282008[/TD]
[TD]Margot 2017 Blanket[/TD]
[TD]8/17/2016[/TD]
[TD="align: right"]$147,442.40[/TD]
[TD="align: right"]$147,442.40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]231122009[/TD]
[TD]Margot 2017 Boxes[/TD]
[TD]9/1/2016[/TD]
[TD="align: right"]$10,300.00[/TD]
[TD="align: right"]$10,300.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]273122010[/TD]
[TD]PO # JVCD[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$10,121.40[/TD]
[TD="align: right"]$10,121.40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]293122011[/TD]
[TD]PO # JGCW[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$5,589.00[/TD]
[TD="align: right"]$5,589.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]231220132[/TD]
[TD]PO # JVGW[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$11,482.56[/TD]
[TD="align: right"]$11,482.56[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]231222013[/TD]
[TD]PO # JGCD[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$11,923.80[/TD]
[TD="align: right"]$11,923.80[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]231522014[/TD]
[TD]PO # JVGC[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$11,493.00[/TD]
[TD="align: right"]$11,493.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]253122015[/TD]
[TD]PO # JV[/TD]
[TD]11/5/2016[/TD]
[TD="align: right"]$11,074.92[/TD]
[TD="align: right"]$11,074.92[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]293122018[/TD]
[TD]TV PO 0916/1T1[/TD]
[TD]12/5/2016[/TD]
[TD="align: right"]$14,260.80[/TD]
[TD="align: right"]$14,260.80[/TD]
[TD][/TD]
[TD] 0
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Mick Peters]

Hi Mark31mark, try this,
Ok so from what I can see there are 2 main parts to this
Firstly if $AM4="Received" then that is a pass (True),
Secondly if all of the following are true
$M4>=5,$Q4>=5,$X4>=5,$AD4>=5,$AG4>=5 then true.
So if you want more than 2 colours then you need to add some extra criteria into your conditional formatting.
I would consider adding 3 rules into your conditional formatting. You can have more than 1 rule applied to a single or set of cells but stack them in order and check the box that stops the rules once one is met.
So for Example (Remove the " from each end of each formula that is so it will display correctly here).
this would check that each cell is greater than 0 and less than to equal to 4
" =IF(OR(AND($A1="Received"),AND($B1>0,$C1>0,$D1>0,$E1>0,$F1>0,$B1<=4,$C1<=4,$D1<=4,$E1<=4,$F1<=4)),"TRUE","FALSE") "
" =IF(OR(AND($A1="Received"),AND($B1>=5,$C1>=5,$D1>=5,$E1>=5,$F1>=5,$B1<=365,$C1<=365,$D1<=365,$E1<=365,F1<=365)),"TRUE","FALSE") "
" =IF(OR(AND($A1="Received"),AND($B1<=-1,$C1<=-1,$D1<=-1,$E1<=-1,$F1<=-1,$B1>=-120,$C1>=-120,$D1>=-120,$E1>=-120,F1>=-120)),"TRUE","FALSE" "

You may have to play with the order to make sure they act in the way you want.
I hope this helps
Mick.