Display a Date when a cell is Modified

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I lifted the code below and tweaked from this NG via Gord Dibben (but
I've done something wrong as it doesn't do anything)

I simply want the date/time that any value is entered/changed in
D9:D144 to display in J24

Could anyone assist? Thanks


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D9:D144")) Is Nothing Then
With Target
If .Value <> "" Then
.Range("J24").Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
 
Hi,

And that is exactly what the code does so a couple of things to check are:-

1. where have you put it? It's worksheet code so right click the sheet tab,
view code and paste in in on the right.

2. Are macros enabled?

3. has some other coe disabled events?

Mike
 
Thanks Mike

I've changed to below and it works. Only issue now is that I have the
sheet protected (except for D9:D144) but it doesn't seem to work when
protected

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect Password:="1234"
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D9:D144"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(24, 10)
.NumberFormat = "dd-mmm-yy at h:mm"
.Value = Now
End With
ActiveSheet.Protect Password:="1234"
Application.EnableEvents = True
End If
End With
End Sub
 
Back
Top