Thread: Timestamping
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
ward376 ward376 is offline
external usenet poster
 
Posts: 360
Default Timestamping

This will put a timestamp in column L whenever a cell in the same row
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Count 1 Then
Exit Sub
Else
If Right(Target.Address, 2) = "$1" Then Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With Me
If Left(Target.Address, 3) = "$A$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$B$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$C$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$D$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$G$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$H$" Then
With Range("l" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
End If
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
End Sub

Cliff Edwards