welshraz
New Member
- Joined
- Apr 29, 2016
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
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?
Thanks all.
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.