Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    355
    Location

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

    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
    Remember: it is the second mouse that gets the cheese.....

  2. #2
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    355
    Location
    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
    Remember: it is the second mouse that gets the cheese.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •