Search code examples
excelvbarowhighlight

How to highlight target column in hidden row in a table?


The following code works to highlight the target row and column in two tables ListObject(1) and ListObject(2) while clearing all hidden rows (Note: ListObject(1) is directly above ListObject(2))

Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim oList1 As ListObject
    Dim oList2 As ListObject
    Dim rng As Range

    Set oList1 = Me.ListObjects(1)

    ' Contains hidden rows when filter is applied
    Set oList2 = Me.ListObjects(2)

    ' Only proceed if `Target` is inside the table body
    If Intersect(Target, oList2.DataBodyRange) Is Nothing Then Exit Sub

    Application.ScreenUpdating = False
    
    'Clear the color of all cells
    Me.Cells.Interior.ColorIndex = 0

    ' Highlights columns
    Set rng = Intersect(Target.EntireColumn, oList1.DataBodyRange)
    If Not rng Is Nothing Then
        rng.Interior.ColorIndex = 24
    End If

    ' Highlights columns
    Set rng = Intersect(Target.EntireColumn, oList2.DataBodyRange)
    rng.Interior.ColorIndex = 24

    ' Highlights rows
    Set rng = Intersect(Target.EntireRow, oList2.DataBodyRange)
    rng.Interior.ColorIndex = 38

    ' Clears all hidden rows
    For Each oRow In oList2.ListRows
        If oRow.Range.EntireRow.Hidden Then
            oRow.Range.Interior.ColorIndex = 0
        End If
        
    Next oRow

    Application.ScreenUpdating = True
End Sub

Question: How do I highlight target column in hidden row on ListObject(2)?

EDIT1:

Tried to highlight hidden target column after clearing hidden rows. But I get a Run-time error '91': Object variable or With block variable not set

    Dim oCol As ListColumn

    ' Clear hidden row and recolor hidden target column
    For Each oRow In oList2.ListRows
        If oRow.Range.EntireRow.Hidden Then
            With Target
                oCol.Range.Interior.ColorIndex = 24
            End With
        End If
        
    Next oRow

Solution

  • You can "reapply" the color formatting to the column(s) in the hidden row(s):

        ' Clears all hidden rows
        Dim oRow As ListRow
        For Each oRow In oList2.ListRows
            If oRow.Range.EntireRow.Hidden Then
                oRow.Range.Interior.ColorIndex = 0
    
                ' Reapply the color to the columns
                Intersect(oRow.Range, Target.EntireColumn).Interior.ColorIndex = 24
            End If
        Next