Conditional Formatting With An IF Function Displaying Colors

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?

I understand that, but first the cell has to look up if it's HEP or ME. If it's HEP the values are allowed to be higher than 10. Does that make sense?

Hey man,

I tried that just like you posted but it still doesn't work and I know it should. IF I set it to D26 > 10, by itself with no "AND" function it works. But when I type what you typed, it doesn't. I THINK it might have to do with the fact that excel is seeing the "ME" as a number and not a text, but I'm not too sure. Any idea on what I should do?

Thanks dude! I would rep you if I could, I have to spread rep. Any help would be appreciated.

The formulae in column C are bringing values across from column CU in the sheet 'Q100 data'. However, if you look at those values directly, you will see that there are several spaces after ME and HEP. It would be wise, therefore to replace the formula in C3 of the Formatted sheet to this:

=TRIM('Q100 data'!CU1)

As the cell is formatted as Text, you should change this to General, and then you can copy down (or use Copy | Paste Special | Formulas if you wish to retain the grey shading). Then the CF formula that Fotis suggested should work for you.

Hope this helps.

Pete

Dude, you guys are awesome. I'll try out both your methods and let you know how it goes. Repped both of you!

Fotis, if you could explain your equation that would be greatly appreciated.