VBA quick change help

[ryan_law2000]

everyone, I have VBA that works great except when a change is made in column G it runs the vlookup formula on all the rows in column H.
Any way to modify this so it only runs the vlookup on the updated row? and not the entire column?
thanks in advance...

If Not Intersect(Target, Sheet4.Range("G9:G1000")) Is Nothing Then
  Dim lRowCount As Long
  lRowCount = Cells(Rows.Count, "G").End(xlUp).Row
  
  With Range("H9").Resize(lRowCount)
    .Formula = "=IFERROR(VLOOKUP([@[Unit '#]],Table_owssvr_1[[#All],[Assigned Unit '#]:[Removal Date]],3),"""")": .Value = .Value
  End With
End If

 

[ryan_law2000]

I have tried this but I keep getting a "Compile Error: Invalid or unqualified reference"
Any ideas? (I have changed the ranges from original post)

If Not Intersect(Target, Sheet38.Range("A9:A110")) Is Nothing Then
If Target.Count > 1 Then Exit Sub

   Dim MyCells As Range
   Set MyCells = Range("A10:A110")
    
    If Not Application.Intersect(MyCells, Range(Target.Address)) _
           Is Nothing Then
       Target.Offset(, 1).Formula = "=IFERROR(VLOOKUP([@[Employee Name]],Table_powerviewsql_JobTracker_Employee[[Full Name]:[Position]],2,),"""")": .value = .value
    End If
End If