I am trying to create a formula in excel 2007 that includes a data validation cell. For example, in cell a1, I have four choices, a,b,c,d in my drop down list. In b1, I need to enter numbers that correspond to each choice in the list (I.e. a has to be between 20 and 25, b has to be between 22 and 24, c has to be between 30 and 32, d has to be between 23 and 27. if the value entered in the cell b1 is outside the desired range for the value in a1, I need b1 to turn red, but leave the original value in the cell. If it is inside the range, I want the cell to turn green. I am not sure how to make this formula. Can I use a vlookup? Or nested if statements? And, how do I address the value if false and value if true if I want my original values to remain but the cell to just turn color?

Try this: Format B1 with a fill color of Red. That will be the default for any value other than the values that fit your criteria.

Then use this Rule in Conditional Formatting, choosing a fill color of Green:

=OR(AND(A1="a",B1>=20,B1<=25),AND(A1="b",B1>=22,B1<=24),AND(A1="c",B1>=30,B1<=32),(AND(A1="d",B1>=23,B1<=27)))

Whenever that OR statement is TRUE, the cell will be Conditionally Formatted with Green.

Note: In your post you used the word "between" e.g.

"between 20 and 25". That means >20 and <25, but does not include 20 or 25. The formula I suggested has equal signs, so itwillinclude your upper and lower bounds for each letter. Remove the equal signs if you don't want them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History