How do I build a loop into this TimeStamp VBA Code

Question

In this code, I'm attempting to incorporate a loop.

It just updates the first value I paste into the range it is referencing; it does not update any of the other cells. Is there a simple method to loop this and update it when I paste a range of cells so that it moves on to the next cell in the row that doesn't equal a blank?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 2
xTimeColumn = 9
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub

If there is anything further I can do, kindly let me know.

In order to record the time, I was anticipating a timestamp function that would let me paste ranges into the spreadsheet. Except for the first cell in the pasted range, it is not updating.

Answer 1

As Target is a range, it may contain a number of Areas, and each Area may contain a number of Cells. You could use a loop to go through those two collections and modify each cell separately.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    Dim xDPRg, xRg As Range
    xCellColumn = 2
    xTimeColumn = 9
    
    Dim Area As Range
    For Each Area In Target.Areas
        Dim Cell As Range
        For Each Cell In Area.Cells
            xRow = Cell.Row
            xCol = Cell.Column
            If Cell.Text <> "" Then
                If xCol = xCellColumn Then
                   Cells(xRow, xTimeColumn) = Now()
                Else
                    On Error Resume Next
                    Set xDPRg = Cell.Dependents
                    For Each xRg In xDPRg
                        If xRg.Column = xCellColumn Then
                            Cells(xRg.Row, xTimeColumn) = Now()
                        End If
                    Next
                End If
            End If
        Next Cell
    Next Area
End Sub

You might want to add an exit condition if Target is too large. If the user makes a large worksheet edit, like clearing an entire column, this event might cause unnecessary slowdowns.