Date modified within Spreadsheet

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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