Date Tracking

[JayEggleton]

I want to track when we clean our bins. Is there anyway that when I enter a new cleaning date in say cell D26 the old date would move to the next cell in the row D27 and keep doing this every time I update the date? This way I can track the dates cleaned and the time between cleaning.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a new cleaning date in cell D26 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 4 Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim oldVal As String, newVal As String
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    If newVal <> "" Then
        Target.Value = newVal
        Target.Offset(1).Value = oldVal
    Else
        Target.ClearContents
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub