intersect range and table.listcolumn - need to find if cell is in table column

[werafa]

hi all,

I'm trying to determine if a selected cell is in a particular column in a table. I don't know how to work with table objects, and can't get this line to work. Can you help?

If Not Intersect(myCell, myTable.ListColumns("Stand Age (Year)")) Is Nothing Then

I want the code to accept multi-cell ranges, but only to act when individual cells fall in the column with the header title 'Stand Age (Year)', and to format the font color for the entire table row if they do. the full code is:

Private Sub Worksheet_Change(ByVal Target As Range)
'test whether target is part of a table, and
'set row font colour if'stand age (year)' column value changes

Dim myCell As Range
Dim myTable As ListObject
'Dim myFlag As Boolean
Dim myRow As Long

    
    For Each myCell In Target
        If IsCellInTable(myCell) = True Then
            Set myTable = myCell.ListObject
            'test whether column = stand age column
            If Not Intersect(myCell, myTable.ListColumns("Stand Age (Year)")) Is Nothing Then
                On Error GoTo CleanUp
                Application.EnableEvents = False
                'get table row (but ignore if is header row)
                myRow = myTable.ListRows(myCell.Row - myTable.HeaderRowRange.Row).Index
                'set font color
                If myCell.Value <> "" Then 'row is 'active'
                    myTable.ListRows(myRow).Font.ColorIndex = 1
                Else
                    myTable.ListRows(myRow).Font.ColorIndex = 48
                End If
            End If
        End If
    Next myCell
       
CleanUp:
   Application.EnableEvents = True
End Sub

thanks in advance
Werafa

[werafa]

got it working.
here is the code in case anyone else is interested

Private Sub Worksheet_Change(ByVal Target As Range)
'test whether target is part of a table, and
'set row font colour if'stand age (year)' column value changes

Dim myCell As Range
Dim myTable As ListObject
Dim myListRange As ListColumn
Dim myRow As Long
Dim colName As String

colName = "Stand Age (Years)"
    
    For Each myCell In Target
        If IsCellInTable(myCell) = True Then
            Set myTable = myCell.ListObject
            'test whether column = stand age column
            
            Set myListRange = myTable.ListColumns(colName)
            If Not Intersect(myListRange.Range, myCell) Is Nothing Then
                On Error GoTo CleanUp
                Application.EnableEvents = False
                'get table row (but ignore if is header row)
                myRow = myTable.ListRows(myCell.Row - myTable.HeaderRowRange.Row).Index
                'set font color
                If myCell.Value <> "" Then 'row is 'active'
                    myTable.ListRows(myRow).Range.Font.ColorIndex = 1
                Else
                    myTable.ListRows(myRow).Range.Font.ColorIndex = 48
                    myTable.DataBodyRange.Cells(myRow, myTable.ListColumns(colName).Index).Font.ColorIndex = 1
                    
                End If
            End If
        End If
    Next myCell
       
CleanUp:
   Application.EnableEvents = True
End Sub