I would like a date/time stamp macro for excel spreadsheet. I have a
situation where I need for a date stamp to be enter automatically in the AG
column of the row if any changes were made in that row. I took a suggestion
from the web and got example A: but it put the stamp 31 columns to the right
of the cell being edited; so I tried example B:, but in order for this to
work I would have to enter ElseIfs for hundreds of rows and I don’t want to
talk about if I add a new column. Did I mention I have over 12 sheets this
needs to done on? Please help! If you can. Thanks

A:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:AE10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 31).ClearContents
Else
With .Offset(0, 31)
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


B:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Me.Range("A2:AF10000")) Is Nothing Then
Application.EnableEvents = False
'With .Offset(0, 1)
With Range("AG2")
..Value = Now
..NumberFormat = "dd/mm/yy hh:mm AM/PM"

With Range("E2")
..Value = " "
End With
End With
End If

ws_exit:
Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Me.Range("A2:AE10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
me.cells(.row,"AG").ClearContents
Else
With me.cells(.row,"AG")
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub




Kokanutt wrote:
>
> I would like a date/time stamp macro for excel spreadsheet. I have a
> situation where I need for a date stamp to be enter automatically in the AG
> column of the row if any changes were made in that row. I took a suggestion
> from the web and got example A: but it put the stamp 31 columns to the right
> of the cell being edited; so I tried example B:, but in order for this to
> work I would have to enter ElseIfs for hundreds of rows and I don’t want to
> talk about if I add a new column. Did I mention I have over 12 sheets this
> needs to done on? Please help! If you can. Thanks
>
> A:
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(Range("A2:AE10000"), .Cells) Is Nothing Then
> Application.EnableEvents = False
> If IsEmpty(.Value) Then
> .Offset(0, 31).ClearContents
> Else
> With .Offset(0, 31)
> .NumberFormat = "mm/dd/yyyy"
> .Value = Date
> End With
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
>
> B:
> Private Sub Worksheet_Change(ByVal Target As Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(.Cells, Me.Range("A2:AF10000")) Is Nothing Then
> Application.EnableEvents = False
> 'With .Offset(0, 1)
> With Range("AG2")
> .Value = Now
> .NumberFormat = "dd/mm/yy hh:mm AM/PM"
>
> With Range("E2")
> .Value = " "
> End With
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End With
> End Sub

--

Dave Peterson