Excel VBA Worksheet_Change for a Range of values

Question

I have a problem with VBA, I need to use the worksheet change event to pickup cell values from AI28 to AI30 and move them over to V28 to V30. This is what I have do so far

Private Sub Worksheet_Change(ByVal Target As Range)

If IsNumeric(Target) And Not (Target = "") Then
  If Target.Address = Range("AI28:AI30").Address Then
   Range("V28:V30").Value = Range("AH28:AH30").Value
   
   Else
      If Target.Cells.Value <> Empty Then Exit Sub
   Exit Sub
   
   End If

End If
    
End Sub

It works fine for just one range eg AI28 and V28 so what am I missing? A loop or something?

Answer 1

Use a loop and Intersect:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("AI28:AI30"))

    If rng Is Nothing Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    Dim cell As Range
    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
           Me.Range("V" & cell.Row).Value = cell.Value
        End If
    Next

SafeExit:
    Application.EnableEvents = True

End Sub