Compile error: Ambiguous name detected

[Aletha]

Hi All,
I searched the forums and i didnt see a topic like this so here it goes
I am trying to program excel so that when someone enters a number the next cell will automatically enter the date and time. I figured it out for the first 2 columns but the subject error comes up for the next columns.
I am attaching the file just in case i screwed up my explanation
Update: i actually did see a thread with this topic.. but im still confused
Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

        With Target

            If .Count > 1 Then Exit Sub

            If Not Intersect(Range("A2:A30"), .Cells) Is Nothing Then

                Application.EnableEvents = False

                With .Offset(0, 1)

                    .NumberFormat = "dd mmm yyyy hh:mm:ss"

                    .Value = Now

                End With

                Application.EnableEvents = True

            End If

        End With

    End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

        With Target

            If .Count > 1 Then Exit Sub

            If Not Intersect(Range("D2:D30"), .Cells) Is Nothing Then

                Application.EnableEvents = False

                With .Offset(0, 1)

                    .NumberFormat = "dd mmm yyyy hh:mm:ss"

                    .Value = Now

                End With

                Application.EnableEvents = True

            End If

        End With

    End Sub

[mrice]

You need to have your Worksheet_change macro sitting in the VBA tab for the sheet for which it is intended to operate and not in a normal module as well.

In this case, it looks like the code needs to be in the Sheet1 tab and be removed from Module2.

[Aletha]

You need to have your Worksheet_change macro sitting in the VBA tab for the sheet for which it is intended to operate and not in a normal module as well.

In this case, it looks like the code needs to be in the Sheet1 tab and be removed from Module2.

Thank you MRICE!
but i still get the same error... and it highlightes the line
Private Sub Worksheet_Change(ByVal Target As Excel.Range)..
any thoughts?

[mrice]

Try the attached...

[Aletha]

Try the attached...

Thank you again MRICE
this is actually what i had in the begining. the problem that i am having is that if i want to do the same thing in the released column it doesnt work. and thats when i get the error screen

[shg]

Aletha,

Try one copy of this in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        
        If Not Intersect(Range("A2:A30, D2:D30"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            With .Offset(0, 1)
                .NumberFormat = "dd mmm yyyy hh:mm:ss"
                .Value = Now
            End With
            Application.EnableEvents = True
        End If
    End With
End Sub