Modify code to insert date/time

[RattlingCarp3048]

I am working on modifying a code someone gave me on here a while back to auto enter the date/time. it has been tremendously helpful and i use it everywhere. However, this time around i am trying to mod it for a specific cell rather that an entire row or column but nothing happend when the cell changes, maybe because the value is a drop list? can someone help mod it so that if cell C29 <>"" then insert date/time in U29?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Range("C29:H29") And Target.Value <> "" Then
Application.EnableEvents = False
Target.Range("U29") = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If
Handler:
End Sub

 

[rollis13]

Use instead:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Intersect(Target, Range("C29")) And Target.Value <> "" Then '<- changed
        Application.EnableEvents = False
        Range("U29") = Format(Now(), "mm-dd-yyyy hh:mm") '<- changed
        Application.EnableEvents = True
    End If
Handler:
End Sub

 

[RattlingCarp3048]

Use instead:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Intersect(Target, Range("C29")) And Target.Value <> "" Then '<- changed
        Application.EnableEvents = False
        Range("U29") = Format(Now(), "mm-dd-yyyy hh:mm") '<- changed
        Application.EnableEvents = True
    End If
Handler:
End Sub

nothing happened when i changed C29. i did a direct copy and paste as-is.

 

[rollis13]

Where did you paste the macro ? since it triggers by Event Worksheet_Change it must be pasted in the sheet's module.

 

[RattlingCarp3048]

Where did you paste the macro ? since it triggers by Event Worksheet_Change it must be pasted in the sheet's module.

correct, thats where i pasted it. the sheet name is Master Packing List and that's the module i pasted it too

 

[rollis13]

Sorry, my mistake, copied the wrong macro, change the line of code to this:

If Not Intersect(Target, Range("C29")) Is Nothing And Target.Value <> "" Then '<- changed

 

[RattlingCarp3048]

Sorry, my mistake, copied the wrong macro, change the line of code to this:

If Not Intersect(Target, Range("C29")) Is Nothing And Target.Value <> "" Then '<- changed

that was it. thank you