Forum Discussion

gabi.slaughter's avatar
gabi.slaughter
Copper Contributor
May 18, 2017

Date fields

I have excel doc that we use to track SOP

How do I get a cell to add system date automatically when the adjacent cell is filled in or changed

 

There are about twenty cells that we fill in as the SOP progresses

 

Thanks

excel
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 18, 2017

    This requires a VBA macro.

    - Right-click on the worksheet tab that needs the automatic date insertion and choose "View Code"

    - Paste this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Exit if cells outside this range were modified:
    If Intersect(Target, Me.Range("C2:C11")) Is Nothing Then Exit Sub
    'Exit if more than one cell was modified
    If Target.Cells.Count > 1 Then Exit Sub
    'place current date in cell one column to the right:
    If Len(Target) > 0 Then
        Target.Offset(0, 1).Value = Date
    Else
        'cell was cleared, remove date
        Target.Offset(0, 1).ClearContents
    End If
End Sub

    - Save-as your file, select Excel Workbook With Macro's 

Resources