Producing Date based on Drop Down

Copper Contributor

I am working to automate a "Project Start Date" field based on a drop down menu. For example, the dropdown menu has three option "Not-started", "Started", and "Completed". Once "Started" is selected in the dropdown menu, I want my "Project Start Date" column to produce the date at that time. I would then like that date to remain indefinitely. Any suggestions on how to accomplish this? Thanks

1 Reply


Let's say you have Project Status drop-downs in D2:D100, and Project Start Date is column F.

Right-click the sheet tab.

Copy the following code into the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    On Error GoTo ErrHandler
    If Not Intersect(Range("D2:D100"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In Intersect(Range("D2:D100"), Target)
            If cel.Value = "Started" Then
                Range("F" & cel.Row).Value = Date ' or Now if you want the time too
            ElseIf cel.Value = "" Then
                Range("F" & cel.Row).ClearContents
            End If
        Next cel
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Switch back to Excle.

Save the workbook as a macro-enabled workbook *.xlsm).

Make sure that you allow macros when you open it.