Posted to microsoft.public.excel.programming
|
|
worksheet_change D.McRitchie
It's brilliant! Thanks! It works perfect!
"Dave Peterson" wrote:
One way:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim NextRow As Long
Set myRng = Me.Range("a:a,b1:c9")
If Intersect(Target, myRng, Me.UsedRange) Is Nothing Then Exit Sub
On Error GoTo errHandler:
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRng, Me.UsedRange).Cells
With Worksheets("sheet2")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = "'" & myCell.Value
.Cells(NextRow, "B").Value = myCell.Address
With .Cells(NextRow, "C")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
Next myCell
errHandler:
Application.EnableEvents = True
End Sub
Myriam wrote:
I found Dave McRitchie's code that would work perfect for my post of 9/10 -
Capture date and data...
But I can't seem to modify it to allow me set as target multiple cells in
diferent locations throughout the sheet and then stamp date and record each
change on another sheet.
---------------------------
I also found the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("sheet2")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
If Target.Address = "$B$4" Then .Cells(x, "a") = Target
End With
End Sub
Which would work perfect if I could combine Dave's code with this one and
make it multiple target cells.
Could you please give me a hand?
As always, very grateful for any help you can give me.
I need to date certain cells as they change and copy them to another sheet.
I only find
--
Dave Peterson
|