Since you're only looking to see if a value is in the list, I'd use
application.match instead of application.vlookup.

And sh.range(rng.address) is overkill if rng is on sh. Just using rng is
sufficient.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vRange As Range
Dim TempTechNo As Integer
Dim sh As Worksheet
Dim rng As Range
Dim ReturnValue As Variant 'could be an error
Dim strMsg As String

Set vRange = Me.Range("g9:g15")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, vRange) Is Nothing Then
Exit Sub
End If

TempTechNo = Me.Range("tech_no").Value

Set sh = Worksheets("Lists")

'Set range based on whether employee is technician or hourly
If TempTechNo 0 Then
Set rng = sh.Range("$A$3:$A$11")
Else
Set rng = sh.Range("$A$15:$A$25")
End If

'Lookup user entry in correct list to confirm that it is valid.
ReturnValue = Application.Match(Target.Value, rng, 0)
If IsError(ReturnValue) Then
strMsg = "The code you entered is incorrect. Try again."
MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
End If

End Sub


Connie wrote:

I have a sheet named "Lists". Cells A3..A11 contain a range which
I've named Technician_Codes. Cells A15..A25 contain a range which
I've named Support_Codes. I am using the Worksheet_Change event to
validate user entry on a sheet named "Field Rep Time Sheet". I am
using the Worksheet_Change event to validate the entry as I have
several validations to do, and the logic is too complicated for the
Data Validation tool.

The user will be entering data in cells g9..15, and the same
validation applies for each cell. In the code below, how do I avoid
hardcoding g9? I want to test the user's entry one by one as input is
made into cells g9..g15. Also, is there any way I can avoid
hardcoding the ranges -- how do I use the range names
(Technician_Codes and Support_Codes) that I've created? This is only
part of the validation that I am doing; however, if I can get through
this, I should be able to figure out the rest.

Thanks.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vRange As Range
Dim TempTechNo As Integer
Dim sh As Worksheet

Set vRange = Range("g9")
TempTechNo = Range("tech_no").Value

Set sh = Worksheets("Lists")

'Set range based on whether employee is technician or hourly
If TempTechNo 0 Then
Set rng = sh.Range("$A$3:$A$11")
Else
Set rng = sh.Range("$A$15:$A$25")
End If

'Lookup user entry in correct list to confirm that it is valid.

ReturnValue = Application.VLookup(vRange, _
sh.Range(rng.Address), 1, False)
If IsError(ReturnValue) Then
strMsg = "The code you entered is incorrect. Try again."
MsgBox strMsg, vbOKOnly + vbInformation, "Invalid Code"
End If

End Sub

--

Dave Peterson