Update a cell with a time.

[Colin Hayes]

HI All

When the content of any of the cells in the range D4:F6 changes , I need
the date and time to be placed in D10.


Can someone help with some code please?


Grateful for any advice.

 

[Don Guillett]

Right click sheet tab>view code>insert this>format d10 as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("d4:f6")) Is Nothing Then Exit Sub
Range("d10") = Now
End Sub

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "D4:F6"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
Me.Range("D10").Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End If
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on sheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[Colin Hayes]

HI

Thanks for the coding - I'm grateful.

I experimented a little and find that it will not put the date and time
where the cells in the specified range are updated from a remote source.


It only works when I update manually directly onto the sheet.

The cells D4:F6 are updated via formula referring to second worksheet in
the same workbook. Can the code be modified to accommodate this?



Best Wishes