Excel – Show timestamp when cell is changed

microsoft excelmicrosoft-excel-2010vba

I need to know how to show a timestamp in a cell in column D when a cell changes in that same row in column B. I edited this screenshot in paint to show what I mean more easily:
The image

I have a vba script that checks a different sheet for a value which it enters in column B. This happens with the select Worksheet_SelectionChange event so I can't simply add:

Range("D3").Value = Now()

Or something similar like that as it will update every time I select something and I only need it to update when the cell in the B column changes. The value in column B only contains the numbers 0, 1 or 2 btw

Best Answer

This VBA code will do it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wk As Workbook
Set wk = ThisWorkbook
Dim ws As Worksheet
Set ws = ActiveSheet
WatchedColumn = 2
BlockedRow = 1
TimestampColumn = 4
Crow = Target.Row
CColumn = Target.Column
If CColumn = WatchedColumn And Crow > BlockedRow Then
    Cells(Crow, TimestampColumn) = Now()
End If

End Sub

You have to copy the code, go to View -> Macros in Excel, Create a new one (any name is valid) and on the left column double click the worksheet where you want to use it (red flag in the picture) and in the right side, paste the code.

Double click on Sheet

This macro modifies the content of the cell on column D whenever theres a change on the same row on column B. The variable BlockedRow protects the first row because it usually has labels, if you have more than one row of labels changed the variable to 2 or more.

If you need to change the columns, make the change on the variables WatchedColumn and TimestampColumn. (A=1, B=2, C=3, D=4,... and so on).

Related Question