VBA code that mimic Enable Selection

Question

I am reaching out to seek your expertise in creating a VBA code that replicates the functionality of the "Enable Selection" option in a pivot table.

Specifically, I would like to achieve the following behavior: whenever a cell is selected within a pivot table, I want all the corresponding data regions (DEPENDENT CESLLS) related to the selected cell to be highlighted in yellow.

Answer 1

Target.PivotCell.PivotRow.Interior.Color = RGB(255, 255, 0)

That's a fake code, PivotRow doesn't exists inside the object model. https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcell#methods

Try the code below.

Andreas.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Here As Range
'In case there is not Pivot table
On Error GoTo Exitpoint
'Find the intersection of the whole Pivot table and the current row/column
Set Here = Union( _
Intersect(Target.EntireRow, Target.PivotTable.TableRange1), _
Intersect(Target.EntireColumn, Target.PivotTable.TableRange1))
'Select it
Application.EnableEvents = False
Here.Select
Target.Activate
Exitpoint:
Application.EnableEvents = True
End Sub

Answer 2

Please note that I have Office Home & Business 2019

Answer 3

Hello Anna, thank you so much for your reply.
It gives an error: Run time error 438 Object doesn't support this property or this method.

Target.PivotCell.PivotRow.Interior.Color = RGB(255, 255, 0)

Answer 4

Hi Rami

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

You can try this VBA code to create a macro that will highlight the corresponding data regions of a selected cell in a pivot table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' Check if the selected cell is in a pivot table If Not Intersect(Target, ActiveSheet.PivotTables(1). TableRange2) Is Nothing Then ' Clear any existing cell highlighting ActiveSheet.Cells.Interior.ColorIndex = xlNone ' Highlight the selected cell and its corresponding data regions Target.Interior.Color = RGB(255, 255, 0) Target.PivotCell.PivotRow.Interior.Color = RGB(255, 255, 0) Target.PivotCell.PivotColumn.Interior.Color = RGB(255, 255, 0) End If End Sub

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.