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