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
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