Autofill date when another cell is filled in (not blank) but need the cell to stay blank and not update when the file is opened.

I have another column with a formula I don't want users to change, so that column is locked and protected when the sheet is protected I get Run-time error '1004': Unable to set the NumberFormat property of the Range class. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range

    'If only one cell at a time should be changed, un-comment the next line

    'If Target.Cells.Count > 1 Then Exit Sub

    'To allow deletions to be bypassed without running the code, un-comment the next line

    If Target.Cells(1).Value = "" Then Exit Sub

    If Intersect(Target, Range("V:V")) Is Nothing Then Exit Sub 'specific range

    'Turn off events to keep out of loops

    Application.EnableEvents = False

     For Each c In Intersect(Target, Range("V:V"))

        If c.Value <> "" Then

            Cells(c.Row,"O").NumberFormat = "m/d/y"

            Cells(c.Row,"O").Value = Date

        End If

    Next c

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

Sorry I initially put A1 & A2 instead of V1 and O1, and I need to make it so when data is entered in column V the date the data was entered displays in column O.

1. Copy this code.

    2) Right-Click the sheet tab of interest.

    3) Select "View Code"

    4) Paste the code into the window that appears.

    5) Save the file as a macro-enabled .xlsm file.

    6) Make changes as needed

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range

    'If only one cell at a time should be changed, un-comment the next line

    'If Target.Cells.Count > 1 Then Exit Sub

    'To allow deletions to be bypassed without running the code, un-comment the next line

    If Target.Value = "" Then Exit Sub

    'To apply to a specific range on the sheet, uncomment the last of these 3 lines

    'and change the range address to the range of interest

    'If Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub 'specific range

    'Turn off events to keep out of loops

    Application.EnableEvents = False

     For Each c In Target

        If c.Value <> "" Then

            c(1, 2).NumberFormat = "m/d/y"

            c(1, 2).Value = Date

        End If

    Next c

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

You can't do that with a formula. You need to use VBA code for this.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module that appears:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1"), Target) Is Nothing Then

        Application.EnableEvents = False

        If Range("A1").Value <> "" Then

            Range("A2").Value = Date

        Else

            Range("A2").ClearContents

        End If

        Application.EnableEvents = True

    End If

End Sub

If you want to datestamp multiple cells, it's slightly more complicated.