Change color of cell depending of value.

Hi!

I'd like to change color on a cell depending on the value typed in the cell. The value in the cell is also depending on a previous value typed in another cell.

https://postimg.org/image/8nmzoc6ct/

Lets say this,
In column A I can choose three different alt.(K,L,M), those three values have different requirenments,

K = 1-3
L= 4-6
M= 7-10

If I type L in column A the requirenment for the corresponding column B.
Lets say that I type in 10 in column B, the requirenment is not met (Should be 4-6). Now I want this cell to be red.


I've tried using VLOOKUP and a helping chart like this,

K	1	3
L	4	6
M	7	10

I have around 100 lines of values and with this method I have to put in all of these manually.
Even the conditional formatting is not working.

https://postimg.org/image/eq9fxe41f/
From the pict above you can see that I have one line for every data input. I would like to have max 3 lines that fullfilles every value input in a column.

Thanks for the help.

Sorry about that!

Look in sheet3 (Column A and B) , this is not a nice solution, but when data is added you get the requirement for that value.
So for every line I get the corresponding requirement (A,B)

The formula you gave me works fine FDibbins but if I have more lines, like now, how should I do it?

Note that the data sheet is not included here.

Thanks!

may be so, but in my first post I said that I wanted the color coding for aprrox 100 lines of data. Then again I understand that it created some confusion, sorry about that.

I attached the file I'm working with now. There are some columns with other data that is deleted. I just kept the data needed in the correct columns.

Thank you for your help.

If you can see my latest file attached. The first thing I want to do is to choose the temperature (95,RT,-40) in column A. When choosing the temp I change the requirement for column E.

For example A3 is -40. The value added in E3 is 0.200. This means that the value is within the critera.

On the other hand we have A15 (95), the value added in E15 is 0.504, this value does not meet the criteria and should be colored.

What you can see in sheet3 is just an attmept to solve the problem.
If you have any other suggestions I'm all for it.

I'm not even sure if I answered your question right now.

Thank you! This worked.

I was thinking of another solution.
If I only use conditional formatting and add this "code":

If E3 is equal to -40 and have values under 0.14 OR over 1.16 change cell color.
If E3 is equal to RT and have values under 0.14 OR over 1.25 change cell color.
If E3 is equal to 95 and have values under 0.56 OR over 1.44 change cell color.

Is it possible to do it this way? and how would I do it?

I tried this with no sucess.

=AND($E3="-40";OR(N3<0.14;N3>1.16))
=AND($E3="RT";OR(N3<0.14;N3>1.25))
=AND($E3="95";OR(N3<0.0.56;N3>1.44))