Oct 03 2021 11:07 PM
i have a table with 23 columns (status column is M, start date is O and end date is P) and i'm trying to add the start and end date whenever the status column changes
I have the status column have only these values (scheduled, inprogress, completed, waiting material)
all rows starts with scheduled as default. (but now it is a mix of all depending on there status)
i want to make it that if the status changes to inprogress the start date column adds today's date
and when the status changes to completed the end date column adds the date.
i hope that makes sense.
thanks for your help :)
Oct 04 2021 12:15 AM
Hi @qabandii
Check the below code, you need to paste it in your VBA editor in the worksheet you like this code to execute.
Private Sub Worksheet_Change(ByVal Target As Range)
'Coded By Faraz Shaikh
Dim ColRng As Range
Dim myStatus As String
Dim myDate As Date
Set ColRng = Intersect(Application.ActiveSheet.Range("M:M"), Target)
If Not ColRng Is Nothing Then
myStatus = ActiveCell.Value
myDate = Now
Application.EnableEvents = False
If myStatus = "inprogress" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = myDate
ActiveCell.Offset(0, -2).Select
ElseIf myStatus = "completed" Then
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = myDate
ActiveCell.Offset(0, -3).Select
End If
Application.EnableEvents = True
End If
End Sub
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.
Oct 04 2021 09:59 PM