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