Need value to be the Table Column Header in lieu of Cell Address

[sspatriots]

This is the same code I was working with yesterday, except I want to add a column. I've revised the code to add a value in the new column, per the line of code directly below. Currently, as you can see it is returning the cell address of the Target. I need to have this actually provide me the column header name in that table for the column referenced in that cell address. That is, if the cell address Q5 for a cell in the table "G2JobList" has a column header called "Payment", then I need "Payment" to appear in the second column in lieu of the cell address. I've shown my updated code below as well.

Any assistance on this would be much appreciated.


Thanks, SS

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address(0, 0)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sSheetName As String
Dim temparr(1 To 1, 1 To 3) As Variant
Dim myRange As Range    'Added, SPS, 10/23/23
Dim tb1 As ListObject
Dim lc1 As ListColumn
Dim tgtJobName As String
Dim BLFormula As String

Set tb1 = Range("G2JobList").ListObject                'Source Table
Set lc1 = tb1.ListColumns("Job Name")
            
tgtJobName = Intersect(Target.EntireRow, lc1.Range).Value
            
BLFormula = "=HYPERLINK(""#""&CELL(""address"",XLOOKUP(RC[-6],Job_Name,G2JobList[Last PMT" & Chr(10) & "Status" & Chr(10) & "Change],""Not found!"",0,1))," & Chr(10) & "" & Chr(10) & "RIGHT(CELL(""address"",XLOOKUP(RC[-6],Job_Name,G2JobList[Last PMT" & Chr(10) & "Status" & Chr(10) & "Change],""Not found!"",0,1))," & Chr(10) & "LEN(CELL(""address"",XLOOKUP(RC[-6],Job_Name,G2JobList[Last PMT" & Chr(10) & "Status" & Chr(10) & "Change],""Not found!"",0,1)))-" & Chr(10) & "SEARCH(""!"",CELL(""address"",XLOOKUP(RC[-6],Job" & _
            "_Name,G2JobList[Last PMT" & Chr(10) & "Status" & Chr(10) & "Change],""Not found!"",0,1)),1))" & Chr(10) & "" & Chr(10) & ")" & _
            ""

Set myRange = Range("G2JobList[[Down" & Chr(10) & "Payment]], G2JobList[[Payment" & Chr(10) & "With" & Chr(10) & "Approval]], G2JobList[[Payment" & _
Chr(10) & "Before" & Chr(10) & "Shipping]]")

    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, myRange) Is Nothing Then Exit Sub

    oldAddress = Target.Address

sSheetName = "Jobs"

If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = tgtJobName
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = Now
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = BLFormula
Sheets("LogDetails").Columns("A:G").AutoFit

Application.EnableEvents = True

End If

End Sub

 

[sspatriots]

I played around with it long enough and came up with the following for that line of code:

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Cells(2, Target.Column).Value

It seems to have worked very well.