none
applying conditional formatting to a pivot table RRS feed

  • Question

  • Hi,

    I have a workbook with a pivot table already in it.

    how do I apply conditional formatting to the entire pivot table? I.e. change font color to red in cells with negative values.

    the issue I can't get reference to the pivot table. VBA code, of course, doesn't work:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

    how to do the same, but in VB 2013?

    please help,

    thanks!

    Friday, May 12, 2017 1:58 AM

Answers

  • What error do you get?  What is the value of PTable?

    The documentation indicates that PivotTables() is a valid method and it should return either a particular table at an index or a collection of all tables.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Sann Friday, May 12, 2017 8:39 PM
    Friday, May 12, 2017 1:06 PM
    Moderator

All replies

  • You should be able to do something very similar to that using either the Office Interop Assemblies, or with ExcelDNA (installed via NuGet).

    Either of those reference assemblies will let you get an instance of a worksheet in Excel and then manipulate it in a similar manner to the VBA.

    -EDIT-

    Sorry, from VB2013 I don't think you have the NuGet Package Manager.  You should consider upgrading to VS2017 (you can install the free community edition).


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Friday, May 12, 2017 2:18 AM
    Moderator
  • Hi Sann,

    According to your description, I find this article that may be helpful to you, please take a look.

    http://www.smartxls.com/vb.net/pivot-table.htm

    Hope it is helpful to you.

    Best Regards,

    Cherry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 12, 2017 5:23 AM
    Moderator
  • thank you for your response, but the suggested article implies using 3rd party software smartxls - I did come across that resource a few times myself :).

    I'm sure there is a way to achieve my goal w/o using any 3rd party software.

    if it can be done in VBA, I should be able to do the same in VB 2013.

    Friday, May 12, 2017 12:18 PM
  • thank you Reed for the response.

    yes, I should be able to by using just VB2013 :), but so far unable to figure this one out.

    I have the following code, but it doesn't work:

    Dim MySelection As Excel.Range
    Dim myPT As Excel.PivotTable

    myPT = xlWorkSheet.PivotTables(PTable)  ' - this is incorrect
    myPT.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel)

    MySelection = xlApp.Selection
    MySelection.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, _ Excel.XlFormatConditionOperator.xlLess, "=0")
    MySelection.FormatConditions(MySelection.FormatConditions.Count).SetFirstPriority()
    With MySelection.FormatConditions(1).Font
                        .Color = -16383844
                        .TintAndShade = 0
    End With
    MySelection.FormatConditions(1).StopIfTrue = False

    how to properly set the MyPT variable?

    thanks!

    Friday, May 12, 2017 12:37 PM
  • What error do you get?  What is the value of PTable?

    The documentation indicates that PivotTables() is a valid method and it should return either a particular table at an index or a collection of all tables.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Sann Friday, May 12, 2017 8:39 PM
    Friday, May 12, 2017 1:06 PM
    Moderator
  • PTable is just a String-type variable to store pivot table names. (I have multiple PTs in my wbook)

    anyhow, as soon as you've mentioned that variable, I analyzed it again and realized that I do not set it correctly during the second iteration before I use it. oops. my bad.

    but that code does work. :)

    thank you for your help! thumbs up!

    Friday, May 12, 2017 2:49 PM
  • Glad you got it sorted out.  :)

    Could you please close the thread by clicking the Mark as answer link at the bottom of one or more appropriate posts?

    Thanks in advance!


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, May 12, 2017 5:46 PM
    Moderator