I want to insert a function into a cell that displays the date another cell
was modified. How can I do this?

j,

Copy the code below, right click on the sheet tab and select "View Code" and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?

This works if I'm making changes in one cell and want the reply to appear in
another cell...however, I can't make it work for changes made to a range of
cells and the reply to show in the another range of cells (much like dragging
a function down a column). I think it's just my syntax in modifying the code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code" and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?

j,

The following will put the record on the same row, but 4 columns over,
recording any changes to the block of cells A1:D100. (So the records are
written in E1:H100)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("A1:D100")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:D100"))
myCell.Offset(0, 4).Value = "Cell " & _
myCell.Address(False, False) & " was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

"jandersen" wrote in message
...
This works if I'm making changes in one cell and want the reply to appear
in
another cell...however, I can't make it work for changes made to a range
of
cells and the reply to show in the another range of cells (much like
dragging
a function down a column). I think it's just my syntax in modifying the
code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code"
and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to
monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?

That was perfect! Thanks!

"Bernie Deitrick" wrote:

j,

The following will put the record on the same row, but 4 columns over,
recording any changes to the block of cells A1:D100. (So the records are
written in E1:H100)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("A1:D100")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:D100"))
myCell.Offset(0, 4).Value = "Cell " & _
myCell.Address(False, False) & " was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

"jandersen" wrote in message
...
This works if I'm making changes in one cell and want the reply to appear
in
another cell...however, I can't make it work for changes made to a range
of
cells and the reply to show in the another range of cells (much like
dragging
a function down a column). I think it's just my syntax in modifying the
code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code"
and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to
monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?

That was perfect! Thanks!

You're quite welcome.

Have a nice weekend.

Bernie

Hi Bernie

I've been looking for formula to do exactly this and have tried it but I
have another formula in the same sheet that starts with

Private Sub Worksheet_Change(ByVal Target As Range)

so I am getting an "Ambiguous name" error.

I tried to change the first line fo the formula to

Private Sub Worksheet_Change2(ByVal Target As Range) or
Private Sub Worksheet_dateChange(ByVal Target As Range)

But the macro will then not work.

Can you advise how I can fix this?

Thanks

Bec G




"Bernie Deitrick" wrote:

j,

The following will put the record on the same row, but 4 columns over,
recording any changes to the block of cells A1:D100. (So the records are
written in E1:H100)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("A1:D100")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:D100"))
myCell.Offset(0, 4).Value = "Cell " & _
myCell.Address(False, False) & " was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

"jandersen" wrote in message
...
This works if I'm making changes in one cell and want the reply to appear
in
another cell...however, I can't make it work for changes made to a range
of
cells and the reply to show in the another range of cells (much like
dragging
a function down a column). I think it's just my syntax in modifying the
code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code"
and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to
monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?

Each sheet's module can have at most one worksheet_Change event.

If you want to monitor changes to different areas in that worksheet, you'll have
to make the code in the single worksheet_change event handle it.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Not (Intersect(Target, Me.Range("A1")) Is Nothing) Then
'A1 changed
MsgBox "A1 changed!" 'do the stuff for this range
ElseIf Not (Intersect(Target, Me.Range("c1")) Is Nothing) Then
MsgBox "C1 Changed!" 'do the stuff for this range
End If

End Sub

Bec G (Oz) wrote:

Hi Bernie

I've been looking for formula to do exactly this and have tried it but I
have another formula in the same sheet that starts with

Private Sub Worksheet_Change(ByVal Target As Range)

so I am getting an "Ambiguous name" error.

I tried to change the first line fo the formula to

Private Sub Worksheet_Change2(ByVal Target As Range) or
Private Sub Worksheet_dateChange(ByVal Target As Range)

But the macro will then not work.

Can you advise how I can fix this?

Thanks

Bec G

"Bernie Deitrick" wrote:

j,

The following will put the record on the same row, but 4 columns over,
recording any changes to the block of cells A1:D100. (So the records are
written in E1:H100)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("A1:D100")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:D100"))
myCell.Offset(0, 4).Value = "Cell " & _
myCell.Address(False, False) & " was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Next myCell
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

"jandersen" wrote in message
...
This works if I'm making changes in one cell and want the reply to appear
in
another cell...however, I can't make it work for changes made to a range
of
cells and the reply to show in the another range of cells (much like
dragging
a function down a column). I think it's just my syntax in modifying the
code
you gave me...please help!

"Bernie Deitrick" wrote:

j,

Copy the code below, right click on the sheet tab and select "View Code"
and
paste the code into the window that appears.

Change the range addresses to correspond to the cells you want to
monitor /
use to record.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A2").Value = "Cell A1 was changed " & _
Format(Now(), "mmm dd, yyyy at hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

"jandersen" wrote in message
...
I want to insert a function into a cell that displays the date another
cell
was modified. How can I do this?







--

Dave Peterson