How do I create a macro that writes the current time in a cell when the value of a function changes?
Posted by Tony Nilsson on September 27, 2000 9:53 AM
Hi. I guess the subject says it all, but here it is again:
How do I create a macro that writes the current time in a cell when the value of a function changes?
E.g. in C3 I have the function =C5*C6
Now when I change the cells so that the
value of the function changes I want the
macro to write the time of change in e.g.
cell E3.
I don't want to report the time if the
value of the function remains the same
e.g. C5 and C6 changes from 0 and 0 to
0 and 5.
Any help would be very appreciated.
Thanks
Tony
Posted by Tim Francis-Wright on September 27, 2000 8:14 PM
This would go in the code for the worksheet
in question (Excel 97 and higher):
Here, C3 is the cell to watch;
D5 is the cell with the timestamp;
and F1 is a (possibly hidden) cell
that keeps track of C3.
Sub Worksheet_Calculate()
If Range("F1").Value <> Range("C3").Value Then
Range("F1").Value = Range("C3").Value
Range("D5").Value = Now
End If
End Sub
HTH
P.S.: In Excel 5.0, you would need to have a macro set
the Worksheet.OnCalculate property to reference
a regular subroutine with the same code.
Posted by Tony Nilsson on September 28, 2000 12:26 PM
Re: Thank you. I will try that as soon as possible :)
