Populating an excel cell with the current date and time on a value change in another cell

Posted by Dave Micklewight on January 11, 2002 8:08 AM

Any ideas how I can populate a cell with the current date and time only when another cell value changes. The other cell is derived via OLE from another source and I want to capture the time when the data is sent to it.

Posted by Tom Urtis on January 11, 2002 8:26 AM

Right click on your sheet tab, left click on View Code, and paste this into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address &LT;&GT; "$A$1" Then Exit Sub
Range("$D$2").Value = Now
End Sub

It assumes that you are monitoring cell A1 for changes in value, and that such changes will display the date and time in cell D2.

Format D2 as desired.

Any help?

Tom Urtis