none
Conditional Formatting for Pivot Field RRS feed

  • Question

  • Hi Team,

    Is there any option to select the "Apply Rule To" option of Conditional Formatting to a Pivot Field with VBA Scripting. I do have a code that can select the Entire field and apply but that makes the "AppliesTo" Range to cells (Static) but not the Field (Dynamic). 

    However, Please find the code that I have presently

    Set PT = ActiveSheet.PivotTables(PivotName)
    Set PF = PT.PivotFields("CPR SPEC")
    PF.DataRange.FormatConditions.Add xlNoBlanksCondition
    With PF.DataRange.FormatConditions(1)
        With .Borders(xlTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        With .Borders(xlLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        With .Borders(xlRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        With .Borders(xlBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
    End With


    Friday, November 30, 2018 10:29 AM

All replies

  • Hi Skuridi,

    Please refer to the following code:

    Dim i as Long
    For i = 1 to ActiveSheet.PivotTables(1).PivotFields.Count
    If ActiveSheet.PivotTables(1).PivotFields(i).Position = 1 Then
        'Enter your formatting for hirearchy level 1 here
    ElseIf ActiveSheet.PivotTables(1).PivotFields(i).Position = 2
        'Enter your formatting for hirearchy level 2 here
    ElseIf ActiveSheet.PivotTables(1).PivotFields(i).Position = 3
        'Enter your formatting for hirearchy level 3 here
    End If
    Next i

    For more information, please see the following links:

    VBA pivot table formatting based on field hierarchy

    conditional formatting pivot table vba

    Pivot Table Conditional Formatting with VBA

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Saturday, December 1, 2018 1:29 AM
  • Hi Skuridi,

     

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, December 4, 2018 3:11 AM