[Solved] Insert date last modified

mknov · Feb 23rd 2004

Argh!! I know I used to know this...

What is the funciton / code to insert into one cell the date and time another cell was last modified.

It needs to work even if the change wasn't applied directly to the cell (i.e. the cell's value changed as a result of a lookup)

Thanks

MKNov

Iridium · Feb 23rd 2004

Haven't tested this btw but give it a go:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


       [a1].Value = "Cell " & Target.Address & " has changed on" & Format(Now, "dd/mm/yy hh:mm:ss")
       
    End If
End Sub

Display More

HTH

mknov · Feb 24th 2004

Hi Iridium,

Thanks. This is cool, but only works if I apply the change directly to the cell. If the value of the cell has changed as a result of me changing another cell which is referenced by that cell's formula, it ignores it.

Does that make sense or am I rambling?!

mknov · Feb 24th 2004

I solved this myself, but with a different workaround. Although I have to admit I don;t understand why it works.

The cells I was talking about were changing as a result of a lookup that was referencing values that changed when a refresh was run (from a web query). In case anyone's interested, this is what I used...

At the end of the refresh sub, I call this one:

'Inserts the date the cells were last updated (the date the refresh ran)

Sub dateconsol()

Range("h2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("h2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

[Solved] Insert date last modified

Participate now!

Share