PDA

View Full Version : [SOLVED] intersect range and table.listcolumn - need to find if cell is in table column



werafa
08-26-2018, 04:02 PM
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
08-26-2018, 11:02 PM
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