VBA to Input Date Based on Formula

[welshraz]

Huge thanks to those who have previously helped with my other query. Unfortunately, I asked for the wrong thing, so here we go again!

I have formulas in column B which pull the status from another tab with a vlookup. What I need is for the date to be input into another cell when that formula changes to one of the following:

When column B = "Acknowledged" put date in column L
When column B = "In Progress" put date in column N
When column B = "Complete" put date in column O

Here's what I have, which works (thank you @Trebor76 ) when the status is manually input, but can it be changed to incorporate worksheet_calculate?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    'BC dates
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            xOffsetColumn = Evaluate("IFERROR(VLOOKUP(""" & StrConv(Rng.Value, vbProperCase) & """,{""Acknowledged"",10;""In Progress"",12;""Complete"",13},2,0),0)")
            If xOffsetColumn > 0 Then
                Rng.Offset(0, xOffsetColumn).Value = Format(Now(), "dd/mm/yyyy")
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
        Next Rng
        Application.EnableEvents = True
    End If
    
End Sub

Thanks all.

 

[Fluff]

Duplicate to: VBA Add Date to Specific Cell Based on Contents of Another Cell

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.