Removing time in cell

[bigbadlee]

Hi everyone,

I have some code that display date and time in a cell when "done" is input into the adjaecent cell. (Any value would be the trigger, though)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count = 1 And Target.Column = 7 Then
Target.Offset(0, 1) = Now

End If
End Sub

Can this be altered so that when "done" is deleted in the adjaecent cell the time/date is removed. At the moment you have to do it manually.

Thanks in advance

[Leith Ross]

Hello bigbadlee,

This will recognize only "Done" in the cell. It is not case sensitive. Any other value will clear the adjacent cell. To prevent a cascade failure, I have added Application.EnableEvents to the macro. This will prevent the macro from calling itself over, and over when the cell value is changed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
If Target.Cells.Count = 1 And Target.Column = 7 Then
  Select Case LCase(Target.Value)
    Case "done"
      Target.Offset(0, 1) = Now()
    Case Else
      Target.Offset(0, 1) = ""
  End Select
End If
Application.EnableEvents = True

End Sub

[bigbadlee]

Hi Leith,

Absolutely fantastic.

Thank you very much for your swift reply