Multiple condition formating

[Zenru]

Alright guys, I feel a bit embarrased to ask help for conditional formating, but I have tried and tried to make this work out myself but I cant accomplish anything. I am just stuck.

I need to do some multiple condition formating here. I have categories which take part of a material inventory.

Categories:
SU
MU
LU
HG
MG
LG


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Material[/TD]
[TD]INV[/TD]
[TD]SU[/TD]
[TD]SU%[/TD]
[TD]MU[/TD]
[TD]MU%[/TD]
[TD]LU[/TD]
[TD]LU%[/TD]
[TD]HG[/TD]
[TD]HG%[/TD]
[TD]MG[/TD]
[TD]MG%[/TD]
[TD]LG[/TD]
[TD]LG%[/TD]
[/TR]
[TR]
[TD]GAG242[/TD]
[TD]100[/TD]
[TD]20[/TD]
[TD]20%[/TD]
[TD]30[/TD]
[TD]30%[/TD]
[TD]5[/TD]
[TD]5%[/TD]
[TD]15[/TD]
[TD]15%[/TD]
[TD]30[/TD]
[TD]30%[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]WRW425[/TD]
[TD]100[/TD]
[TD]70[/TD]
[TD]70%[/TD]
[TD]10[/TD]
[TD]10%[/TD]
[TD]5[/TD]
[TD]5%[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]15[/TD]
[TD]15%[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]TH2542[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]10[/TD]
[TD]10%[/TD]
[TD]40[/TD]
[TD]40&[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]50[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]QE6477[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[TD]10[/TD]
[TD]10%[/TD]
[TD]70[/TD]
[TD]70%[/TD]
[TD]20[/TD]
[TD]20%[/TD]
[TD]0[/TD]
[TD]%[/TD]
[/TR]
</tbody>[/TABLE]


Basically,


If there is something in SU, but SU% is lower than all categories %, then material should be RED
If there is nothing in SU but there is something in MU, and MU% is lower than the other categories, then material should be colored YELLOW.
If there is nothing in MU, but there is something in LU, and LU% is lower than other categories, then material should be LIGHT BLUE
If there is nothing in LU, but there is something in HG, and HG% is lower than other categories, then material should be PURPLE
If there is nothing in HG, but there is something in MG, and MG% is lower than other categories, then material should be ORANGE

If SU% is higher than the other categories, then material should be GREEN.

I added the colors to the font here, but I want it to be a background color in Excel.

Thanks in advance,

 

[Gerald Higgins]

OK let's do these one at a time.

Let's make some guesses about cell references - the word "MATERIAL" is in cell A1.

For the first condition . . .

=AND(C2>0,D2< F2,D2< H2,D2< J2,D2< L2,D2< N2)
<f2,d2<h2,d2<j2,d2<l2,d2<n2)

and format as red fill.

Note - with the data you provided, row 2 with GAG242 should not actually be red.

Does this work for you ?</f2,d2<h2,d2<j2,d2<l2,d2<n2)