How to get Excel Spreadsheet to auto populate date

Question

Trying to get a macro enabled worksheet on Excel to auto populate date and time when any values are entered in column B or C.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Integer
  For i = 2 To 100
    If Cells(i, "B").Value <> " " And Cells(i, "C").Value = " " Then
      Cells(i, "F").Value = Date & " " & Time
      Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"
    End If
  Next
  Range("F:F").EntireColumn.AutoFit
End Sub

is there anything wrong with the code I'm writing?

Answer 1

You don't want to run through all of that everytime anything on the worksheet changes; only when something that affects the validity of the timestamp changes. Typically, we would use Intersect to determine if one of the values that changed should receive a new timestamp. You also do not want the routine to attempt to run on top of itself so turning event handling off before changing a value (i.e. adding the time stamp) is recommended.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:C")) Is Nothing Then
        On Error GoTo SafeExit
        Application.EnableEvents = False
        Dim bc As Range   'no sense in declaring something until we actually need it
        For Each bc In Intersect(Target, Range("B:C")) 'deal with every cell that intersects. This is how to handle pastes into more than one cell
            If Not IsEmpty(Cells(bc.Row, "B")) And Not IsEmpty(Cells(bc.Row, "C")) Then
                Cells(bc.Row, "F").Value = Now 'Now is the equivalent of Date + Time
                Cells(bc.Row, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"
            End If
        Next bc
        'Range("F:F").EntireColumn.AutoFit 'this slows things down. you may want to comment this out and just set an apprpriate column width that will handle everything
    End If
SafeExit:
    Application.EnableEvents = True
End Sub

That is my take on this old problem. There are many examples. Look toward the Related section down the right-hand side of this page for links to a few.

Answer 2

"Target" will be the cell(s) that changed. It is possible to change more then one cell at a time (via ctrl-enter) so checking all cells in the Target isn't a bad idea.

If you use the Intersect method it will get only the area of Target and the range you wanted to check that overlaps. This will then loop through those cells (if there are any) and if a value is found, timestamp them.

As others have mentioned, disabling events before you plug the stamps will prevent calling another worksheet change event. Just be careful when debugging not to leave events off.

You can read more about the event parameters here: https://msdn.microsoft.com/en-us/library/office/ff839775.aspx

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Excel.Range
    Dim cll As Excel.Range
    Set rng = Excel.Intersect(Target, Range("B:C"))
    If Not (rng Is Nothing) Then
        Excel.Application.EnableEvents = False
        For Each cll In rng.Cells
            If Len(cll.Formula) > 0 Then
                Cells(cll.Row, 6).Value = Format$(Now, "m/d/yyyy h:mm AM/PM")
            End If
        Next
        Range("F:F").EntireColumn.AutoFit
        Excel.Application.EnableEvents = True
    End If
End Sub

Answer 3

Couple of small changes:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Integer
  Application.EnableEvents = False
    If Target.Column = 2 Or Target.Column = 3 Then
      For i = 2 To 100
        If Cells(i, "B").Value <> " " And Cells(i, "C").Value = " " Then
          Cells(i, "F").Value = Date & " " & Time
          Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"
        End If
      Next
    End If
  Range("F:F").EntireColumn.AutoFit
  Application.EnableEvents = True
End Sub

Turn the even off so you don't fire it when your code makes a modification and test the target column to see if it is B or C and only fire if it is

Also, you know your code will update rows 2 to 100 regardless of which row was changed right? If you only want the row that was changed you can get that with target.row