Date Stamp: date last changed Macro

  • Thread starter Thread starter Rachelynn
  • Start date Start date
R

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 A2:D10), 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
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:D10"), .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)
 
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("A2:D10"), .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
 
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("A2:D10"), .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)
 
Back
Top