Excel 2013 Automatic Date

Question

Hello I am new to vba so I need some help. I have a spreadsheet that I use every day in which I have to enter the current date in column A, a file number (just one + the previous file number) in to column B and other information from C on. How do I make it so that when I input the file number in Column B todays date automatically fills into column A. I tried =If(B2="","",IF(A2="",NOW(),A2)) in the cell but doesn't work so I tried vba. Heres a few vba's which is did not work:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

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

    Application.EnableEvents = False

        Target.Offset(, 1) = Now

    End If

    Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 And Target.Offset(0, 3).Value = "" Then

        Target.Offset(0, 3) = Format(Now(), "HH:MM:SS")

    End If

End Sub

Answer 1

You were close. . . .

This sheet event code will place time in A when anything entered in B

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B1:B100")) Is Nothing Then

    Application.EnableEvents = False

        Target.Offset(, -1) = Format(Now, "hh:mm:ss")

    End If

    Application.EnableEvents = True

End Sub

Gord

Answer 2

Hi Brian

Work with a Table. In Tables, formulas are auto copied down until the end of the table

Answer 3

Perhaps you have events disabled.

In Immediate Window type  application.enableevents = true  then hit Enter

Gord

Answer 4

I tried and still nothing is happening in column A

Answer 5

Update: I tried a simple if statement: =IF(ISBLANK(B1),"",NOW()) but now I have to drag it down for  the whole A column, this spread sheet will go into the thousands so If there's any way to save me time so that I don't need to drag it down after every days input please let me know.