Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time
Suppose your worksheet requires frequent entries in a column, and you want to automatically record the date and time of whenever a cell in that column changes.
In the picture, when Forecast cells in range C5:C16 are changed, the date and time of that change instantly gets placed into column D on the same row.
This requires a Worksheet_Change event procedure. Right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5:C16")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub
i have one sheet. i want if any data change in column1, column2, column3, and column4 so i can stamp date and time in column 5 ( means any data will change in the sheet stamp date shows up on column 5) please help me in this
In your worksheet module, this would accomplish what you ask for:
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column > 4 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Cells(Target.Row, 5)
.Value = Now
.NumberFormat = "MMM DD, YYYY, hh:mm AM/PM"
End With
End Sub