Date stamp needed with referance to another cell content

Question

My need is getting date punched automatically in "P" when some one select "Closed" in "O". I already have the below code in the same sheet.

Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Target With Cell If .Column = Range("D:D").Column Then Cells(.Row, "A").Value = Now() End If End With Next Cell For Each Cell In Target With Cell If .Column = Range("M:M").Column Then Cells(.Row, "J").Value = Now() End If End With Next Cell End Sub

and O is validated with list Open, closed (selecting only one at a time).

Can anyone help me to add the code into the codes i already have.

Answer

The below code will do what you wanted.

-Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
      
   If Target.Columns.Count + Target.Rows.Count = 2 Then 'Execute only if single cell value is change
   
              
       With Target
               
           If .Value = "Closed" Then
               Cells(.Row, "P").Value = Now()
               
           ElseIf .Value = "Open" Then
               Cells(.Row, "P").ClearContents 'Clear value in column P
               
           End If
                               
       End With
       
       
   End If
   
   Application.EnableEvents = True
   

End Sub - Let me know if it works for you. Cheers.