This says that only if a cell in Agency State column changes do the work
If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then
This says to activate the cell just to the right of myRange
myRange.Offset(0, 1).Activate
IMO, it is best practice to use Event Subs to only check which and where the Event occurred, then to direct the execution flow to the appropriate Sub procedure.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Calc As Long
With Application
Calc = .Calculation
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'This assumes that there are three different Events that might occur and you want to handle each differently
If Not Intersect(Target, myTable.ListColumns("Agency_State").Range) Is Nothing Then SuberbValidate Target
If Not Intersect(Target, SecondEventRange) Is Nothing Then Sub2 Target
If Not Intersect(Target, ThirdEventRange) Is Nothing Then Sub2 Target
With Application
.Calculation = Calc
.EnableEvents = True
End With
End Sub
Private Sub SuberbValidate(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
ApplySuburbValidation Cel
Next Cel
End Sub
Private Sub Sub2(ByVal Target As Range)
'do something with SecondEventRange
End Sub
Private Sub Sub3(ByVal Target As Range)
'do something with ThirdEventRange
End Sub