Hey all,

I've been stuck on this problem for a while now and I can't seem to figure it out. Long story short my company has received an oil analysis machine that takes samples from both engines. 1 engine type is "ME", and the other "HEP". Now I just wanted to do some simple conditional formatting but since the machine puts the HEP & ME in the same column, it's a lot trickier than I thought it would be. For example, Zinc values are drastically different on HEP than they are for ME. So continuing on the Zinc example, I was wondering if there's anyway I can do something like:

So if Zn is located at cell D26 and the engine class (HEP or ME) is located at C26 I wanted it to lookup if C26 reads "HEP or ME"..if it's ME I want it to follow this rule:

If D26 > 10, highlight cell in red.
If D26 < 7, highlight cell in green.
If D26 is 8-10, highlight in yellow

Now if instead of ME C26 says HEP, I want it to follow a similar set of rules. Is this possible? I've tried If functions in conditonal formatting and it just isn't working. How do I do this?