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...

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Is this what you are after

Please Login or Register  to view this content.

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.

Try this code:

Please Login or Register  to view this content.

Thanks Jason, you came through again. It works flawlessly

No problem. I just took mudraker's code and modified it slightly based on your revised ranges.