Auto timestamp

Hi Moun and welcome to the Forum

Here's one way to do it...

In the attached file, cells A4:A23 contain an imaginary 5-digit order number (custom formatted to show leading zeroes) and B4:B23 show the status of each order. Change a value in one of those yellow cells and the event macro below will add a timestamp to the appropriate row and column. I've added comments so you can see what's happening:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Based on tutorial from TeachExcel.com

Dim Status As Range
Dim Datestamp As Range

'Your data table range
Set Status = Range("B4:B23")

'Check if the changed cell is in the Status column or not.
If IsEmpty(Target) Or Intersect(Target, Status) Is Nothing Then Exit Sub

' Check that only one cell was changed (or quit)
If Target.Count > 1 Then
    MsgBox "Please change only one cell at a time (no datestamps were added)"
    Exit Sub
End If

'Stop events from running
Application.EnableEvents = False

'Determine cell / column for the date/time
Set Datestamp = Cells(Target.Row, Status.Column + Left(Target.Value, 1))

' Add the date/time
Datestamp.Value = Now

'Turn events back on
Application.EnableEvents = True

End Sub

It works as follows: B4:B23 have data validation so you can only pick a value from the dropdown or delete contents to make it a blank. The dropdown is based on the values in hidden cells C2:F2 which have a numbered 5 stage process like 1. Ordered, 2. Shipped, 3. Delivered, 4. Invoiced and 5. Paid (to match the corresponding date headings in C3:F3).

If a yellow cell is changed, the macro uses the VBA Left string function to strip out the number (e.g. 2 from 2. Shipped) then adds the timetamp that many columns to the right of the yellow. cell.

Hope this makes sense and works for you (in which case please don't forget to mark this Answer selected).

In proctice, you'd probably want to protect the sheet (e.g. so the timestamps can't be overwritten accidentally) but that's a separate question.