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