Help on VBA code
 

Hi,

I am totally new with VBA and in need of urgent help. I need to have the
timestamp in just one cell, say A4, when an entry is made to any of the cells
in range A4:z500. Based on mcgimpsey's code, how do I change it to have it
work based on my need above? I only know enough to change the range, but I
do not know how to change the code to have the timestamp in just one cell
(A4).

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

Thanks a heap!

Maeglin

Help on VBA code
 

Maeglin wrote...
....
timestamp in just one cell, say A4, when an entry is made to any of the cells
in range A4:z500. Based on mcgimpsey's code, how do I change it to have it
....

If you want a timestamp in cell A4, and the entry range includes A4,
what happens when an entry is made in A4? I'll assume the entry range
is actually A5:Z100.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:Z100"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Range("A4").ClearContents
Else
Range("A4").NumberFormat = "dd mmm yyyy hh:mm:ss"
Range("A4").Value = Now
End If
Application.EnableEvents = True
End If
End With
End Sub

Help on VBA code
 

Oh, I'm sorry... I meant I wanted the timestamp to show in A3 for the range
A4:Z500. Thanks!

"Harlan Grove" wrote:

Maeglin wrote...
....
timestamp in just one cell, say A4, when an entry is made to any of the cells
in range A4:z500. Based on mcgimpsey's code, how do I change it to have it
....

If you want a timestamp in cell A4, and the entry range includes A4,
what happens when an entry is made in A4? I'll assume the entry range
is actually A5:Z100.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:Z100"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Range("A4").ClearContents
Else
Range("A4").NumberFormat = "dd mmm yyyy hh:mm:ss"
Range("A4").Value = Now
End If
Application.EnableEvents = True
End If
End With
End Sub