none
Updating Conditional Formatting in Pivot Tables with VBA RRS feed

  • Question

  • I am trying to programmatically use some of the conditional formatting options, but I can't find anything on how to do what I need.

    I have a pivot table that identifies products in various codes. I want to compare the codes and their products to standard product offerings, which are outside the pivot table. There are several standards that users will need to compare their codes against, hence my need to programmatically update the conditional formatting.

    I have been able to manually create the correct conditional formatting, and it looks like this - green matches the standard, red does not:

    I need to use the selected "Apply Rule to" option, and I need to use the formula option for the rule type. Of course, this is only one of the two rules I am applying. Prod Name and Contract are the intersection fields in my Pivot Table - Availability is my value.

    I found this code, and it effectively allows me to highlight all the correct cells, and update the conditional formatting - but this code doesn't set the correct "Apply Rule to" or the correct "Formula" rule type. Has anyone successfully set these variables via vba?

    Here's the code and what the conditional format rules look like after I run the code. I also do NOT want the formatting to stop if True, which is also being set by this code...?

        mCompare = ActiveSheet.DropDowns("CompareSelect")
        Range("d6").Select
        Do Until mCompare = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
        Loop
        mCol = Mid(ActiveCell.Address, 2, 1)

        ActiveSheet.PivotTables("StandardComparison").PivotSelect "", xlDataOnly, True
        Selection.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=AND($" & mCol & "8>0,K8>0)"
        Selection.FormatConditions(1).Interior.Color = 3407718
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$" & mCol & "8<>K8"
        Selection.FormatConditions(2).Interior.Color = 8420607

    HELP! Thanks - Beth

    Monday, August 7, 2017 6:08 PM

Answers

  • Check if it works.

         
         Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($" & mcol & "8>0,K8>0)"
         Selection.FormatConditions(1).Interior.Color = 3407718
         Selection.FormatConditions(1).StopIfTrue = False
         
         
         Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" & mcol & "8<>K8"
         Selection.FormatConditions(2).Interior.Color = 8420607
         Selection.FormatConditions(2).StopIfTrue = False
         



    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by BethL VA Wednesday, August 9, 2017 4:42 PM
    Wednesday, August 9, 2017 9:22 AM
    Answerer

All replies

  • Hi Beth,

    Thanks for visiting our forum.

    Then here we mainly focus on general questions about Office client. Since your query is about using VBA in Excel, we'll move your thread to the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Tuesday, August 8, 2017 3:17 AM
  • Repeat the manual process of Conditional formatting once more. This time keep macro recording on (View->Macros->Record Macro)

    After the process is over, click Stop Recording from same menu.

    Then press Alt+F11. VB Editor will appear. Search for Module1 or Module2 etc in left pane.

    You will find some code there. Paste it here. It can be modified to suit exactly as per your need.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, August 8, 2017 9:25 AM
    Answerer
  • Recording the macros doesn't show steps with conditional formatting - tried that first, as it is usually my code starting point.
    Tuesday, August 8, 2017 1:02 PM
  • Thank you Yuki - I never get my forums right!
    Tuesday, August 8, 2017 1:03 PM
  • Check if it works.

         
         Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($" & mcol & "8>0,K8>0)"
         Selection.FormatConditions(1).Interior.Color = 3407718
         Selection.FormatConditions(1).StopIfTrue = False
         
         
         Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" & mcol & "8<>K8"
         Selection.FormatConditions(2).Interior.Color = 8420607
         Selection.FormatConditions(2).StopIfTrue = False
         



    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by BethL VA Wednesday, August 9, 2017 4:42 PM
    Wednesday, August 9, 2017 9:22 AM
    Answerer
  • Thanks Asadulla - this may be as close as I can get!

    My manual management of the rules allows me to exclude total columns in my conditional formatting by using the "Applies to" options in the rule management menu:

    Whereas this code applies the conditional formatting to the selected cells (which I do via code -
    ActiveSheet.PivotTables("StandardComparison").PivotSelect "", xlDataOnly, True)

    I think now, I can select the pivot table totals and delete the conditional formatting rules!

    Thanks for your help! Beth

    Wednesday, August 9, 2017 4:42 PM