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

Material	INV	SU	SU%	MU	MU%	LU	LU%	HG	HG%	MG	MG%	LG	LG%
GAG242	100	20	20%	30	30%	5	5%	15	15%	30	30%	0	0%
WRW425	100	70	70%	10	10%	5	5%	0	0%	15	15%	0	0%
TH2542	100	0	0%	10	10%	40	40&	0	0%	0	0%	50	50%
QE6477	100	0	0%	0	0%	10	10%	70	70%	20	20%	0	%

<tbody>
</tbody>

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)