You need to use WorksheetFunction.VLookup twice to find the low and high
price, then do the compares, then do whatever you want with the price if it
is/is not within range.

Worksheetfunctiuon/VLookup needs a range  as its second parameter. You can use
a named  range in the spreadsheet but you need to express it as

Worksheets("worksheetname").range("rangename")

(If I didn't tell you that you would waste a couple of hours wondering why the
function was not working!)

this would work just fine, but I need
a different method of looking up the low/high price.

The first of the two tables looks like this:

Column A B C D E F
Date | Product ID | Prod Name | Units Sold | Sales Price | Total

When i enter a price in column  E, I need the code  to look up the
ProductID in the same row  (i.e cellChanged.Offset (0, -3)) and with
that ProductID I would need to look up low/high prices  in the second
table that resides indifferent worksheet. that table looks as follows:

Col. A B C D
Product ID | Product Name | Low price | high  price |

Hope this makes more sense. I think I need to use the Find method but
I am not sure how. If you can help?

I think VLookup will work fine.

Let us assume that the product  table is a named range  called "tblProductList"
on a worksheet called "Products"

then use:

HighPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" &
Target.Row), Worksheets("Products").Range("tblProductlist"), 4, false)

LowPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" & Target.Row),
Worksheets("Products").Range("tblProductlist"), 3, false)

If Target.Value>LowPrice and Target.Value<HighPrice Then

'Do something with it

End If

(The long lines will undoubtedly get wrapped and you will need to unwrap them.
I have left balnk line between each line of code.)

You were right, it worked just fine. Thank you for all the help