Date modified within Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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!
 
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
 
That was perfect! Thanks!

Bernie Deitrick said:
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
Click to expand...
 
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
 
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
 
Hi Dave

Thanks for getting back to me. I am still failing at combining the two
statements. The one I have already is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("w5:CB3146"))
If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If
End Sub

The person I am building the model for originally said all they wanted was
the cell colour to change if the data changed. Now they have also requested a
column called Adjustment Date and I need to not only show the cell colour
change in the data range but also need to put the date in the column which is
a couple of columns to the right of the range.

Can you help?

Bec G
 
So...
Where does the date go if I change W5?
Where does the date go if I change CB5?
Where does the date go if I change w5:cb3000?

Does the date go in one cell for each row or one cell for each cell that could
be changed?
 
Dave

If you change W5 or CB5 I need a date to be populate in CE5.
If you change any cell in A5:cb3000 i need the date to appear in the row
that has changed in colum CE
In other words any change on any row between column A and column CB needs to
generate a date to be populated in column CE on the row that has changed.

In the W:CB range I need the colour of the cell that has been changed to
change colour - hence the range in the macro below.

Does this make sense?
 
So if I changed W5:CB5 (all 58 cells) at once--or any number of them, I'd just
change CE5, right?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToChk As Range
Dim myIntersect As Range
Dim myOneColRng As Range
Dim myCell As Range

Set myRngToChk = Me.Range("A5:cb3000")

Set myIntersect = Intersect(Target, myRngToChk)

If myIntersect Is Nothing Then
Exit Sub
End If

'just one cell per row that got a change
Set myOneColRng = Intersect(myIntersect.EntireRow, Me.Columns(1))

Application.EnableEvents = False
For Each myCell In myOneColRng.Cells
With Me.Cells(myCell.Row, "CE")
.NumberFormat = "dd-mmmm-yyyy"
.Value = Date
End With
Next myCell
Application.EnableEvents = True

End Sub

Personally, I think I'd want the date and time in that cell:

With Me.Cells(myCell.Row, "CE")
.NumberFormat = "dd-mmmm-yyyy hh:mm:ss"
.Value = Now
End With
Dave

If you change W5 or CB5 I need a date to be populate in CE5.
If you change any cell in A5:cb3000 i need the date to appear in the row
that has changed in colum CE
In other words any change on any row between column A and column CB needs to
generate a date to be populated in column CE on the row that has changed.

In the W:CB range I need the colour of the cell that has been changed to
change colour - hence the range in the macro below.

Does this make sense?
Click to expand...
 
No for each row that has a change in it I want to capture the date on that
row in CE. I like the idea of the time as well as date too.
 
I don't understand, then.

W5:CB5 is a single row.
No for each row that has a change in it I want to capture the date on that
row in CE. I like the idea of the time as well as date too.
Click to expand...
 
Sorry Dave sent that off in a hurry, it does what I want for populating the
date. I also need the cell colour of the cell I have changed to change
colour. Is that possible?

Thanks again for all your help
 
I recorded a macro when I changed the fill color for a cell.

This is what the code looked like:

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

So you'll want to add a group of lines that look like that (you'll want to
record your own macro to get the color you want):

Set myIntersect = Intersect(Target, myRngToChk)

If myIntersect Is Nothing Then
Exit Sub
End If

'add this portion
With myIntersect.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

.....

In my test workbook, .colorindex = 6 is yellow.
Sorry Dave sent that off in a hurry, it does what I want for populating the
date. I also need the cell colour of the cell I have changed to change
colour. Is that possible?

Thanks again for all your help
Click to expand...
 
Dave

I had this one (below) that was working but when I try to put either it or
the one you just sent me in with the macro you gave me that puts in the date
neither macro works. Is there a way for them both to work?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("w5:CB3146"))
If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If
End Sub
 
The target is the range that changed.

But the range that changed can be a single cell or multiple cells. You could
change all the cells that changed--but that means some of the changing cells
could be outside the range you really care about.

That's why the code I suggested uses the intersection of the target (the range
that changed) and the range you care about.

So drop that second worksheet_change event procedure and add those 4 lines that
I suggested in the previous post.

But change the 6 to a 3:

Dave

I had this one (below) that was working but when I try to put either it or
the one you just sent me in with the macro you gave me that puts in the date
neither macro works. Is there a way for them both to work?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("w5:CB3146"))
If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If
End Sub
Click to expand...
 
ps. If this were the only _change event you were using, this portion:

If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If

Should probably be:

If Not isect Is Nothing Then
isect.Interior.ColorIndex = 3
End If

Then you'd be only coloring the cells that changed that are in that
intersection.
Dave

I had this one (below) that was working but when I try to put either it or
the one you just sent me in with the macro you gave me that puts in the date
neither macro works. Is there a way for them both to work?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("w5:CB3146"))
If Not isect Is Nothing Then
Target.Interior.ColorIndex = 3
End If
End Sub
Click to expand...
 
Back
Top