Solved: Nesting in a Private Sub Worksheet_Change

[Xrull]

I need to know what I'm doing wrong here.
This code works:

    Private Sub Worksheet_Change(ByVal Target As Range)
   
  Dim rng As Range, rng2 As Range
   
  If Target.Column > 1 Then Exit Sub
  Set rng2 = Sheets("Data").Range("A2:E2000")
  For Each rng In Target
      If Len(rng) > 0 Then
          rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
          rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
      Else
          rng.Offset(, 1).Resize(, 2).ClearContents
      End If
  Next rng
   
  End Sub

I want to have 2 looks ups or more, but I keep getting a message when I try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
   
  Dim rng As Range, rng2 As Range
  Dim rng3 As Range, rng4 As Range
  If Target.Column = 1 Then
  Set rng2 = Sheets("Data").Range("A2:E2000")
  For Each rng In Target
      If Len(rng) > 0 Then
          rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
          rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
      Else
          rng.Offset(, 1).Resize(, 2).ClearContents
          End If
     Next rng
     
  If Target.Column = 6 Then
  Set rng4 = Sheets("Data").Range("G2:H2000")
  For Each rng3 In Target
      If Len(rng3) > 0 Then
          rng3.Offset(, 1) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
          rng3.Offset(, 2) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
      Else
          rng3.Offset(, 1).Resize(, 2).ClearContents
      
      End If
  Next rng3
  End Sub

How can I get the last code to work?
Thanks,
Xrull

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range

If Target.Column = 1 Then
Set rng2 = Sheets("Data").Range("A2:E2000")
For Each rng In Target
If Len(rng) > 0 Then
rng.Offset(, 1) = WorksheetFunction.VLookup(rng, rng2, 3, 0)
rng.Offset(, 2) = WorksheetFunction.VLookup(rng, rng2, 5, 0)
Else
rng.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng

ElseIf Target.Column = 6 Then
Set rng4 = Sheets("Data").Range("G2:H2000")
For Each rng3 In Target
If Len(rng3) > 0 Then
rng3.Offset(, 1) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
rng3.Offset(, 2) = WorksheetFunction.VLookup(rng3, rng4, 2, 0)
Else
rng3.Offset(, 1).Resize(, 2).ClearContents
End If
Next rng3
End If
End Sub
[/vba]

[Xrull]

xld,
That's what I was looking for.
Xrull