VBA Changes Only in Specific Table

[JMKnapp]

I have a worksheet that contains several named tables and some "free range" data. In one specific table only (named "TestTable"), each time I change data in one column (column I, "Fee Delta") I would like the neighboring cell (column J, "Change Date") to mark the data / time of the change. Additionally, I would like the code to reference the column names rather than letters, so that if columns are added or deleted the code can adapt. I am close, but need help.

My VBA code works to an extent, but I have two problems:

1. My code property finds column I by title ("Fee Delta") even when columns are added / removed, but I cannot figure out how to contain its range so that it only works inside the table. Right now, any change I make in column I results in a timestamp in column J, even if it's not in the target table.
2. I can't figure out how to reference my target column (J, "Change Date") by name the same way that I could my trigger column.

Locking the affected area with a set range (i.e., I6:I13 and J6:J13) won't work because I will be adding rows to the table on a regular basis and need to range to by dynamic.

Here's what I have so far. Any ideas? (And thank you!)

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("TestTable[Fee Delta]").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "J").Value = Now
Else
Cells(Cell.Row, "J").Value = ""
End If
End If
Next Cell
End Sub

 

[cmowla]

Let me know if this code works as intended:

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date

Dim tbl As Range
Set tbl = Sheets(Target.Parent.Name).ListObjects("TestTable").DataBodyRange

If Not Intersect(Target, tbl) Is Nothing Then
    Dim modifiedColumnName As String, modifiedDateColumnName As String
    Dim modifiedColumnNumber As Integer, modifiedDateColumnNumber As Integer
    modifiedColumnName = "Fee Delta"
    modifiedDateColumnName = "Change Date"
    
    Dim i As Integer
    For i = 1 To tbl.Columns.Count
        If tbl(0, i).Value = modifiedColumnName Then modifiedColumnNumber = i
        If tbl(0, i).Value = modifiedDateColumnName Then modifiedDateColumnNumber = i
    Next i
    
    Dim cell As Range
    For Each cell In Target
        If cell.Column = modifiedColumnNumber Then
            If cell.Value <> "" Then
                Cells(cell.Row, modifiedDateColumnNumber).Value = Now
            Else
                Cells(cell.Row, modifiedDateColumnNumber).Value = ""
            End If
        End If
    Next cell
End If

End Sub

 

[JMKnapp]

Thank you for giving a crack at this!! Unfortunately, this doesn't seem to solve the issues. Using this code, no changes inside the table in any cell result in a timestamp. Changing the value in cell H2 (outside of the table) results in a timestamp in that same cell. I don't understand why though ... I don't see anything in the VBA code that would reference that cell.

 

[cmowla]

Okay, try this modification (I didn't consider table offsets! . . . I just added + tbl.Column - 1 and application.enableevents = false/true so that this sub isn't called twice unnecessarily.)

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date

Dim tbl As Range
Set tbl = Sheets(Target.Parent.Name).ListObjects("TestTable").DataBodyRange

If Not Intersect(Target, tbl) Is Nothing Then
    Dim modifiedColumnName As String, modifiedDateColumnName As String
    Dim modifiedColumnNumber As Integer, modifiedDateColumnNumber As Integer
    modifiedColumnName = "Fee Delta"
    modifiedDateColumnName = "Change Date"
   
    Dim i As Integer
    For i = 1 To tbl.Columns.Count
        If tbl(0, i).Value = modifiedColumnName Then modifiedColumnNumber = i + tbl.Column - 1
        If tbl(0, i).Value = modifiedDateColumnName Then modifiedDateColumnNumber = i + tbl.Column - 1
    Next i
    Dim cell As Range
    For Each cell In Target
        If cell.Column = modifiedColumnNumber Then
            Application.EnableEvents = False
            If cell.Value <> "" Then
                Cells(cell.Row, modifiedDateColumnNumber).Value = Now
            Else
                Cells(cell.Row, modifiedDateColumnNumber).Value = ""
            End If
            Application.EnableEvents = True
        End If
    Next cell
End If

End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("TestTable[Fee Delta]")) Is Nothing Then
      If Target.Value <> "" Then Target.Offset(, 1).Value = Now
   End If
End Sub

 

[cmowla]

Ah, so to consider changes to multiple cells as @JMKnapp originally wanted, to prevent the sub from being called once for each cell that was changed (just be called once for any number of changed cells in the Fee Delta column), and to ensure decent speed in updating multiple cells,

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date

If Not Intersect(Target, Range("TestTable[Fee Delta]")) Is Nothing Then
    Application.EnableEvents = False

    Dim previousCalculation As Boolean
    previousCalculation = Application.Calculation
    Application.Calculation = xlCalculationManual

    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("TestTable[Fee Delta]")) Is Nothing Then
            If Trim(cell.Value) <> "" Then
                Cells(cell.Row, Range("TestTable[Change Date]").Column).Value = Now
            Else
                Cells(cell.Row, Range("TestTable[Change Date]").Column).Value = ""
            End If
        End If
    Next cell
End If

Application.EnableEvents = True
Application.Calculation = previousCalculation

End Sub