Populating a New Static Date When a Drop Down List is Changed

[ahthomas]

Hello,

I am trying to track some testing specimens at work, and I would like to have to automatically populate a date when the status drop down menu is changed. For example, I have a status column located in column G. The status has a drop down menu including Not Received, Specimens Received, Machining, QC&Dim, NDI, Conformity, Drying, Conditioning, Instrumentation, and Testing. I would like the column next to this status column, so column H to populate the date that the status drop down menu is changed. I have been trying to use some other codes, but nothing seems to be working. Any help would be greatly appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("G"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 1).Value = Date
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[ahthomas]

This worked incredibly well. Thank you!

Is there a way to add the time to this as well so it is also a date and a time?

 

[Peter_SSs]

Is there a way to add the time to this as well so it is also a date and a time?

Sure

c.Offset(, 1).Value = Date
c.Offset(, 1).Value = Now()