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 ha...
excel
Formulas and Functions
Macros and VBA
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