VBA to Input Date Based on Formula

Status
Not open for further replies.

welshraz

New Member
Joined
Apr 29, 2016
Messages
25
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?

VBA Code:
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Status
Not open for further replies.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,698
Messages
5,765,991
Members
425,321
Latest member
stefanov07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top