cond. format column referencing value in adjacent cell that is drop down list

[Slingbob]

Amps vs. Rod Size.

There should be an attached file that goes along with this description....

In column "C" I have a cell which has a drop down list using Data Validation. In column "F" I have a cell for the user to insert the amperage they gauged.

They select the rod size used in column "C" from the drop down list, and then type in the measured amperage in the cell in column "F".

If drop down list selection is 1/8", then cell in "F" column must contain a number between 75 and 130 or conditional format is to show red background.
If drop down list selection is 5/32" then cell in "F" column must contain a number between 90 and 185 or conditional format is to show red background.
If drop down list selection is 3/16" then cell in "F" column must contain a number between 130 and 225 or conditional format is to show red background.

I'd like to do this with conditional formatting, but that might not be the correct way to get it done. I don't want to do it with Visual and have to use macros.
This is one item in a form that I'm building, and I would like to keep it simple and hopefully monkey proof.

Any help would be appreciated.

Bob

[AliGW]

Welcome to the forum!

Firstly, setup your lookup table in this way:

Excel 2016 (Windows) 32 bit

	H	I	J
20	rod size	amps low	amps high
21	1/8"	74	131
22	5/32"	89	186
23	3/16"	130	225

Sheet: Sheet1

Then you can use this formula for your CF rule:

=OR(F14 < VLOOKUP(C14,$H$21:$J$23,2,0),F14 > VLOOKUP(C14,$H$21:$J$23,2,0))

where $H$21:$J$23 is your lookup table.

Of course, the lookup table can be as long as you need it to be and could be on another sheet or in hidden columns.