How to apply conditional formatting to a calculated field in a PivotTable using VBA?

Applying conditional formatting to a calculated field in a PivotTable using VBA can be a bit tricky because calculated fields are dynamic and may not be directly referenced like regular range of cells. However, you can achieve this by navigating through the PivotTable’s data area and applying conditional formatting to the range that corresponds to your calculated field.

Below are step-by-step instructions, alongside the corresponding VBA code to achieve conditional formatting in a PivotTable:

Step-by-Step Instructions

Identify the Calculated Field: Before you apply conditional formatting, ensure you know the name of the calculated field in your PivotTable.
Access the PivotTable: Use VBA to access the specific PivotTable you want to format.
Locate the Data Range: Find the cell range within the PivotTable that corresponds to the calculated field.
Apply Conditional Formatting: Use Excel’s conditional formatting functionality to apply the desired formatting rules to the located range.

VBA Code Example

Below is an example of how you can apply conditional formatting to a calculated field in a PivotTable using VBA:

Sub ApplyConditionalFormattingToCalculatedField()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim rng As Range
    Dim dataRange As Range

    ' Set the worksheet and PivotTable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    Set pt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable name

    ' Specify the name of the calculated field
    Dim calculatedFieldName As String
    calculatedFieldName = "YourCalculatedFieldName" ' Change to the name of your calculated field

    ' Loop through PivotFields to find the calculated field
    For Each pf In pt.DataFields
        If pf.SourceName = calculatedFieldName Then
            ' Get the data range for the calculated field
            Set dataRange = pf.DataRange
            Exit For
        End If
    Next pf

    ' Verify that the dataRange was found
    If Not dataRange Is Nothing Then
        ' Apply conditional formatting
        With dataRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100") ' Example condition
            .Interior.Color = RGB(255, 199, 206)  ' Example color
            .Font.Color = RGB(156, 0, 6)          ' Example font color
        End With
    Else
        MsgBox "Calculated field not found!", vbExclamation
    End If
End Sub

Explanation of the Code

Set the Worksheet and PivotTable: Modify the `ws` and `pt` assignments to refer to the correct worksheet and PivotTable.
Loop Through PivotFields: The code loops through each data field in the PivotTable to find the one matching your calculated field’s name.
Set and Check `dataRange`: The `dataRange` variable will hold the range of data for your calculated field.
Apply Conditional Formatting: The `FormatConditions` property is used to add and customize a conditional formatting rule.

Ensure the PivotTable name, worksheet name, and calculated field name are correctly referenced. Adjust the example condition and formatted style to match your specific needs.

facebooklinkedin