Adding More than One Worksheet_Change Function

I have a bit of code someone on this board provided and I want to make it work for two different ranges. If I just paste it, I get an ambiguous name error. How do I make it work for a second range?

thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = [A2:A101]
If Intersect(Target, rng) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Target.Offset(0, 4).Value = ""
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 4).Value = Application.UserName
Target.Offset(0, 1).Value = Date
End If
End Sub

Hello B.Baumgartner,

The second range in this example, D2:D101, is the same size as the first, but they can be different sizes. You need to use the Union method...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim rng As Range
    Set rng = [A2:A101]
    rng = Union(rng, [D2:D101])
      If Intersect(Target, rng) Is Nothing Then Exit Sub
        If IsEmpty(Target) Then
           Target.Offset(0, 4).Value = ""
           Target.Offset(0, 1).Value = ""
        Else
          Target.Offset(0, 4).Value = Application.UserName
          Target.Offset(0, 1).Value = Date
        End If
End Sub

Sincerely,
Leith Ross

Heres another way of doing it.

And as you are changing cell contents via the macro you should also use Application.EnableEvents in your code to stop the macro change event being triggered when the macro changes the contents.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, _
      Range("A2:A101", "D2:D50")) Is Nothing Then
   Application.EnableEvents = False
   If IsEmpty(Target) Then
      Target.Offset(0, 4).Value = ""
      Target.Offset(0, 1).Value = ""
   Else
      Target.Offset(0, 4).Value = Application.UserName
      Target.Offset(0, 1).Value = Date
      End If
   End If
   Application.EnableEvents = True
End Sub

Thanks Leith and Mudraker!

What if I want to make the target.offset values different for the second range?

You could probably use something like:

If Target.Column = 1 Then  ' for column A
    ' your code
Else                       ' for column D
    ' your code
End If

Is this what you are after

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, _
      Range("A2:A101")) Is Nothing Then
   Application.EnableEvents = False
   If IsEmpty(Target) Then
      Target.Offset(0, 4).Value = ""
      Target.Offset(0, 1).Value = ""
   ElseIf Not Application.Intersect(Target, _
      Range("D2:D50")) Is Nothing Then
      Target.Offset(0, 4).Value = Application.UserName
      Target.Offset(0, 1).Value = Date
      End If
   End If
   Application.EnableEvents = True
End Sub

I couldn't get that last code to work.

Here's what I want to happen:

When a value in column A is entered, the date the selection was made is put in column B and the person's username is put in column E.

This shared spreadsheet tracks two different events for a single product.

So later on when the record is followed up on, I want the person to enter a value in column L. And when that value is entered I want column M to insert the date and column N to insert the person's username.

The code I entered in the first post works great for the Column A, B, E interaction, but I need it to work for the Column L, M, N interaction too.

thanks for all the suggestions so far.