This is a discussion on How to highlight/count rows in a Pivot Table within the Excel Questions forums, part of the Question Forums category; Hello - I have a pivot table and need to apply conditional formatting. I have tried selecting the entire column, ...
I have a pivot table and need to apply conditional formatting. I have tried selecting the entire column, and that works fine until you filter or resort the pivot table.
I found that if you highlight only the values within the pivot table the conditional formatting will remain after a filter.
I am trying to automate the creation of the pivot table and the associated conditional formatting. However, since the data is dynamic, how can I highlight the data within the Pivot table? The number of rows can change each time.
I recorded a macro that does what I want, however "B42" is dynamic. Is there a keystroke combination or someway to select just the data that is within the Pivot Table?
Perhaps there is a keystroke combination that will highlight an entire column of data within a Pivot Table. If not, can you help me on how I can tell the program to select all the data in Column B within the Pivot Table?
Thank you very much in advance.
Code:' Need to be able to select all the data within the pivot table from B5 to Bn' Range("B5:B42").Select ' I can then apply the conditional formatting below. Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=1", Formula2:="=1000" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 5287936 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = True Selection.FormatConditions(1).ScopeType = xlSelectionScope