Need to have Worksheet_Change event include value from another column using structured reference

[sspatriots]

Hi,

I've tried to modify the code below to show the cell value from another column called "Job Name" using structured references. I'm showing in the code below where I have inserted some lines of code to do this, however, it only gives me the column number and not the information in the adjacent cell that is in that column. I see why it is giving me the column number because I have "col1" equal to the "lc1" column identified. I'm just not sure how to make it give me the value in that column that corresponds to that target row.

Any assistance would be much appreciated, Thanks SS

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





'ADDED THIS CODE FROM HERE....    
            Dim tb1 As ListObject
           
            Dim lc1 As ListColumn
        
            Dim col1 As Long
            
            Set tb1 = ListObjects("G2JobList") 'Source Table
            
            Set lc1 = ListObjects("G2JobList").ListColumns("Job Name")
        
            col1 = lc1.Range.Column
'TO HERE....




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, Range("P:R")) Is Nothing Then Exit Sub
    If Intersect(Target, myRange) Is Nothing Then Exit Sub

'    MsgBox "Passed!"                              '<- added for testing
'    If Target.Count = 1 Then oldValue = Target.Value
    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 = ActiveSheet.Name & " - " & Target.Address(0, 0)




'ADDED THIS LINE
            Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = col1




Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now

Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", _
SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress

Sheets("LogDetails").Columns("A:E").AutoFit

Application.EnableEvents = True

End If

End Sub

 

[Micron]

I can't relate the code to what you're asking but it seems that you need to use the Offset property of the range, column or target. For example, if you want to move right 2 columns from Target (the cell that was edited which triggered the event) that could be Target.Offset(0, 2). Part of the problem for me is that the code is all left justified. If you're going to use code tags (and you should) it should be properly indented to improve readability. The copious amount of double spacing doesn't help either.

 

[Alex Blakenburg]

See if this helps:

            Dim tb1 As ListObject
            Dim lc1 As ListColumn
            Dim tgtJobName As String
            
            Set tb1 = Range("G2JobList").ListObject                'Source Table
            Set lc1 = tb1.ListColumns("Job Name")
            
            ' To just get the value
            tgtJobName = Intersect(Target.EntireRow, lc1.Range).Value
            
            ' OR set the cell reference
            Dim cellJobName As Range
            Set cellJobName = Intersect(Target.EntireRow, lc1.Range)

 

[sspatriots]

See if this helps:

            Dim tb1 As ListObject
            Dim lc1 As ListColumn
            Dim tgtJobName As String
           
            Set tb1 = Range("G2JobList").ListObject                'Source Table
            Set lc1 = tb1.ListColumns("Job Name")
           
            ' To just get the value
            tgtJobName = Intersect(Target.EntireRow, lc1.Range).Value
           
            ' OR set the cell reference
            Dim cellJobName As Range
            Set cellJobName = Intersect(Target.EntireRow, lc1.Range)

That did exactly what I was after. Thank you

 

[Alex Blakenburg]

You're welcome. Thanks for letting us know.