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