Show timestamp when cell is changed

Question

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

Answer 1

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.

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).

Answer 2

I know there's already an answer, but this VBA is a bit cleaner -

Private Sub worksheet_change(ByVal target As Range)
 If Not Intersect(target, Range("B:B")) Is Nothing Then
  target.Offset(0, 2) = Now()
 End If
End Sub

To adjust for your other requirement not in the question just add an if -

Private Sub worksheet_change(ByVal target As Range)
    If Not Intersect(target, Range("B:B")) Is Nothing Then
        If target.Row > 1 Then
           target.Offset(0, 2) = Now()
        End If
    End If
End Sub

Answer 3

I would like to add a date in a column in excel after I scan a bar code. After I scan the bar code, excel searched the document and then highlights that row of information. In the highlighted row I would like a date added. Currently I have a code written to double click to add the date but would like the date to be added after the row is highlighted.

Below is my code to search my number after bar code scan and once it is found it highlights that row of information.

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Columns("M")) Is Nothing Then
    Z = Intersect(target, Columns("M")).Value
    If IsNumeric(Z) Then
        x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
    Else
        x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
    End If
    If Not IsError(x) Then
        Application.Goto Cells(x, 15)
    End If
End If

Below is where I can double click in my box to add date. I would like to add the date automatically after the row is highlighted in color.

End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    If Not Intersect(target, Range("K4:K1500")) Is Nothing Then
        Cancel = True
        target.Formula = Date
    End If
End Sub

thank you