I wanted to loop through a large workbook with lots of PivotTables to set a common conditional format on the DataBodyRange of each PivotTable.

Here’s my initial PivotTable with no conditional formatting:


I would like change the text to red for any value that is less than 0.97 :

Option Explicit

Sub PTConditionalFormatting()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim rng As Range
        Dim dblLow As Double
        Dim dblHigh As Double
    'Excel environment - speed things up
        Application.ScreenUpdating = False
    'Initialize variables
        Set wb = ThisWorkbook
        dblLow = 0
        dblHigh = 0.97
    'Loop all PivotTables in all worksheets in the workbook
    'Set conditional formatting
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                Set rng = pt.DataBodyRange
                Call FormatRange(rng:=rng, _
                                 dblValueHigh:=dblHigh, _
            Next pt
        Next ws
    'Tidy up
        'Destroy objects
            Set wb = Nothing
        'Restore Excel environment
            Application.ScreenUpdating = True    
End Sub
Private Sub FormatRange(rng As Range, _
                        dblValueHigh As Double, _
                        dblValueLow As Double)

    With rng
        .FormatConditions.Add(Type:=xlCellValue, _
                              Operator:=xlBetween, _
                              Formula1:=dblValueHigh, _
                              Formula2:=dblValueLow).Font.Color = vbRed

    End With
End Sub


Very helpful if you have a lot of PivotTables to loop through. The Sub() takes a Range Object as one of the arguments. This means you could use it and pass it any Range Object – not just from PivotTables – Worksheet Range, ListObject Range, other PivotTable Range.

Additional PivotTable Resources

  1. Contextures
  2. Peltier Technical Services, Inc.
  3. Chandoo
, , ,

3 comments untill now

  1. Very Nice Code, good

  2. […] PivotTable Conditional Formatting […]

  3. […] PivotTable Conditional Formatting […]

Add your comment now