Forum Discussion

qabandii's avatar
qabandii
Copper Contributor
Oct 04, 2021

Date entry when a cell changes to a specific value (EXCEL 2016)

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 🙂 

excel
Formulas and Functions
Macros and VBA

2 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Oct 04, 2021

    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.

Resources