Date Stamp: date last changed Macro

[Rachelynn]

I'm just starting to learn VBA, so I need a little help with this one!

This code works how it is now, but to do what I want it to, how can
modify this code to have it for example use multiple columns in a lis
(Range A210), and put the date last change was made in any of th
columns in E1:E10.
Make any sense?

Thank You,
Rachel


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
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Su

 

[Bob Phillips]

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rachelynn]

This worked except the date did not appear in the correct row, ended u
about 3 rows down, also appeared in column F if changes were made i
column B, and column G if changes were made in column C. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A210"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Cells(.Row, "E")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Su

 

[Bob Phillips]

Rachel,

Try this, I shouldn't have had a dot before Cells

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A210"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Cells(.Row, "E")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)