Check the values in a column against a set range

[Jester23]

Hey Guys,

I am building an upload tool in Excel where a user can insert (in bulk) roughly 5 Columns of data and up to 1000 rows. Prior to the SQL upload, I would like to check the values input by the user to see if they match exactly to the pre-defined names within named ranges. (I will bring these into another sheet from a SQL Table as the values have a tendency to change)
I am trying to incorporate a check against a column range (A2:A1000) against a defined range (we will call it fruit).
FRUIT = (Banana, Apple, Orange, Lemon)

I would like to check the range (A2:A1000) to see if the value DOES NOT equal the value in the range (FRUIT). If it does not equal a value from the named range of FRUIT in a specific cell within this range, I would like an error message to reflect the Cell which does not meet the criteria and stop the remainder of the checks but more specifically, the SQL upload.
(The stop would mean that the upload to the tables via SQL would not take place until the user corrected the incorrect cell value. At which time they could re-run their upload and proceed.

I will be incorporating this check for 3 to 5 columns where each column is a specific named range on a seperate worksheet named "Variable".

 

[Jester23]

I am able to get this to work by creating a range for the LNGROW but I cannot get this to work so that if it arrives at an empty cell. I need it to stop at the empty cell and prompt the user whether to continue or NOT. The users input the data into this worksheet. Then they simply click a button and it runs through the checks to see if the values they provided match the values in a named range. If they do not, it tells them where the issue is (first one) and they have to correct it and run it again. If it arrives at a blank cell, the user needs to be prompted to stop or move forward. Why? If it is a mistake and a value was missed, the user has the option to stop the process, correct the issue and re-rerun the process. If it is the last cell, they would want to move forward and continue with the next sub and the SQL upload.

Thank you in advance for any assistance you can provide.
-------------------------------

Sub test()
Dim str As Variant
Dim lngRow As Long
Dim datarng As Range
Dim inputsheet As Worksheet
Dim validsheet As Worksheet

Set inputsheet = ActiveWorkbook.Sheets("User Inputs")
Set validsheet = ActiveWorkbook.Sheets("Validation Data")
Set datarng = ActiveWorkbook.Sheets("Validation Data").Range("ModTypes")
inputsheet.Activate
'I need this to run until it gets to an empty cell and then prompt the user to stop or move forward
For lngRow = 2 To 14
str = Application.VLookup((inputsheet.Range("A" & lngRow)), datarng, 1, False)
If IsError(str) = True Then
MsgBox "Please correct your ISSUE in cell A" & lngRow & " and try again."
inputsheet.Range("A" & lngRow + 1).Select
Exit Sub
End If
Next
Set datarng = Nothing
Set inputsheet = Nothing
Set validsheet = Nothing
Set str = Nothing
Call test2
End Sub