Pivot Table Conditional Formatting with VBA

A reader encountered problems applying conditional formatting to a pivot table. I tried it myself, using the same kind of formulas I would have applied in a regular worksheet range, and had no problem. The reader responded that he was having problems in Excel 2007, and I was using 2003. Apparently in 2003 the conditional formatting is preserved when the table is refreshed, but in 2007, the CF in the data range is wiped out.

Well, this is just the type of thing Bill gates invented Visual Basic for. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. I’ll use arbitrary data and an arbitrary condition for the example.

Here is a data source for a simple pivot table. The entire range of values in column D consists of random integers between 1 and 9, and there are three categories by which they can be sorted.

Pivot Table Data Source

The first pivot table, PivotTable1, has the category fields all placed in the rows area. I manually typed the name “Pivot Table 1” in the cell above the pivot table.

Pivot Table 1 Unformatted

Tip: To name a pivot table, right click on the table, choose Table Options, and edit the name in the first line of the dialog.

The second pivot table, PivotTable2, has one category in the columns area, so the values are separated into two columns.

Pivot Table 2 Unformatted

I used the following VBA procedure to apply bold text and yellow fill formatting to rows in PivotTable1 if the value in the Total column is 7 or greater. We are testing the entire DataBodyRange, which is one column wide.

Sub FormatPT1()
  Dim c As Range
  With ActiveSheet.PivotTables("PivotTable1")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .DataBodyRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

I used the next procedure to apply the same formatting to PivotTable2 rows if the value in column a under Category 3 is 7 or greater. The DataBodyRange is two columns wide, for pivot items “a” and “b”. To test against pivot item “a”, we test the pivot item’s DataRange.

Sub FormatPT2()
  Dim c As Range
  With ActiveSheet.PivotTables("Pivottable2")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .PivotFields("Category 3").PivotItems("a").DataRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

The two procedures above belong in a regular code module.

Here is PivotTable1 with the conditional formatting applied.

Pivot Table 1 Formatted

Here is PivotTable2 with the same formatting applied.

Pivot Table 2 Formatted

Note that refreshing the pivot tables changes values but does not automatically reformat the tables. You have to manually rerun the VBA routines, or capture the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Select Case Target.Name
    Case "PivotTable1"
      FormatPT1
    Case "PivotTable2"
      FormatPT2
  End Select
End Sub

This procedure goes in the code module for the worksheet that contains the pivot tables. To access this module easily, right click on the sheet tab, and choose View Code. If you use this approach, you can put the first two procedures into the worksheet’s code module, instead of a regular module.

 

Peltier Tech Charts for Excel

Comments

  1. Hello Jon,

    I think that for you and your readers it should be interesting to see new approaches to pivot tables and charts. We recently integrated a reporting component developed in flex from http://www.flexmonster.com. So far we are thrilled with capabilities and user feedbacks have been most positive

  2. Sebastien Labonne says:

    Interesting solution.

    I can confirm the problem. It seems to occur because in 2007, conditional format is applied to a field, whereas in 2003, it’s applied to a range.

    Without resorting to macros, it’s possible to quickly reapply the conditional format in 2007 by following these steps:
    – Set the conditional format to range covering more than the pivot table (e.g. on cell above).
    – When the pivot is refreshed, go to the “Conditional formatting Manage Rules…” dialog and edit the “Applies to” range.

    Ideally, that range would extend above the pivot table because extending below it does not avoid a complete wipe-out if the pivot table stretches too much.

  3. Hello Jon,

    I cannot thank you enough for exploring this topic further. I am trying it out now on one of my workbooks, and get stuck on the part “For Each c In pt.PivotFields(“TEXT1″).DataRange.Cells”. I am trying to focus in on one of my row labels, and thought I was heading in the right direction (this is just my latest attempt, I have tried entering a few different things).

    Here is what I have in My Worksheet>View Code right now:
    Private Sub SpinButton1_Change()
    Sub FormatPT1()
    Dim c As Range
    With ActiveSheet.PivotTables(“PivotTable1”)

    ‘ reset default formatting
    With .TableRange1
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With

    ‘ apply formatting to each row if condition is met
    For Each c In pt.PivotFields(“TEXT1”).DataRange.Cells
    If c.Value = “TEXT2” Then
    With .TableRange1.Rows(c.Row – .TableRange1.Row + 1)
    .Font.Bold = True
    .Interior.ColorIndex = 6
    End With
    End If
    Next

    End With
    End Sub

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Select Case Target.Name
    Case “PivotTable1”
    FormatPT1
    End Select
    End Sub

    Thank you again for the help!!

  4. Two errors. (1) The line Private Sub SpinButton1_Change() should not be there. (2) you have not declared nor defined pt.

    One of the pivot items in pivot field TEXT1 is TEXT2, right? If you focus in on cells containing the pivot item caption, you will only see the first row with that pivot item.

    Try:

    With ActiveSheet.PivotTables(1)
        With Intersect(.PivotFields("TEXT1").PivotItems("TEXT2").DataRange.EntireRow, _
                .TableRange1)
            '' apply formatting here
        End With
    End With
     
  5. Out of curiosity, under what conditions is this supposed to be happening? The big change to conditional formatting behavior in Excel 2007 PivotTables is that formatting is applied (to be more accurate, can be applied) to the entire column – the point being that as the data in the column is updated with new rows, CF extends to include the new rows. It makes no sense that CF should be blown away instead.

  6. Colin –

    I haven’t checked it out fully. In 2003, CF is applied to ranges, even within pivot tables. I’ve heard that CF can be applied to fields in the pivot table in 2007, much as number formatting can be in 2003. Perhaps because the CF is applied to the range in my test and not to the fields, it was cleared. This can happen when you apply number formats to a range in a 2003 pivot table. (Or perhaps like so much in 2007, it just happens.)

  7. Hmmm, I tried using formulas for various ranges in the PivotTable, but CF stuck every time I refreshed the table. I’ll try again later with a more complex table.

  8. I tried on two fairly simple pivot tables, and I applied the CF to the cells, not to the pivot table DataRanges. The CF remained in the rows area but was cleansed from the data area.

  9. Hello again,

    I am now having issues with this line:

    With .TableRange1.Rows(c.Row – .TableRange1.Row + 1)

    Here is what I have so far, including what I think you meant with your last suggestions (and Text1 is one of my Row Labels, and Text is one of the names populating – and what I wanted to base my ‘condition’ off of):

    Sub FormatPT1()
    Dim c As Range
    With ActiveSheet.PivotTables(“PivotTable1”)

    ‘ reset default formatting
    With .TableRange1
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With

    With ActiveSheet.PivotTables(1)
    With Intersect(.PivotFields(“text1”).PivotItems(“text2″).DataRange.EntireRow, _
    .TableRange1)
    ” apply formatting to each row if condition is met
    For Each c In PivotTables.PivotFields(“text1”).DataRange.Cells
    If c.Value = “text2” Then
    With .TableRange1.Rows(c.Row – .TableRange1.Row + 1)
    .Font.Bold = True
    .Interior.ColorIndex = 6
    End With
    End If
    Next
    End With
    End Sub

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Select Case Target.Name
    Case “PivotTable1”
    FormatPT1
    End Select
    End Sub

    Thank you!!

  10. LEM –

    Some of that stuff is left over from the previous approach. Here:

    Sub FormatPT1()
      Dim c As Range
    
      With ActiveSheet.PivotTables("PivotTable1")
    
        '' reset default formatting
        With .TableRange1
          .Font.Bold = False
          .Interior.ColorIndex = 0
        End With
    
        With Intersect(.PivotFields("text1").PivotItems("text2").DataRange.EntireRow, _
                       .TableRange1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End With
    End Sub
     
  11. Jon! It is working! Thank you for all of the help!!!

  12. Hello Jon,

    Your posting on this topic has been a great help to me.

    Your examples have the subtotal and grand total rows removed from the pivot table. I want to keep the subtotal and grand total rows in my pivot table. Is there a way to conditionally format the pivot table as shown below, but leave the subtotal and grand total row formats unaffected. Say the subtotal and grand total rows are set to color index 14, with bold font prior to applying the conditional formatting. Is there a way to keep this format while conditionally formatting the rest of the pivot table?

    Sub FormatPivotTable()
        Dim c As Range
       
        Set c = Range("B15:H500") 
        'Keeps headers from being formatted
    
        With ActiveSheet.PivotTables("PivotTable1")
            For Each c In .PivotFields("Text").DataRange.Cells
                If c.Value = 1 Then
                    With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
                       .Interior.ColorIndex = 6
                    End With
                End If
            Next
        End With
    End Sub
     

    Any help would be greatly appreciated. Thank you!

    Shanna

  13. Your line

        Set c = Range("B15:H500")
     

    doesn’t do anything. The line

            For Each c In .PivotFields("Text").DataRange.Cells
     

    sets c to a different range, wiping out the reference to B15:H500.

    I think all you need to do is simply change the size of the range being formatted, reducing it by one column. In the totals (i.e., last row) you aren’t likely to have a total of 1, so I didn’t do anything to skip this row.

    Sub FormatPivotTable()
        Dim c As Range
       
        Set c = Range("B15:H500") 
        'Keeps headers from being formatted
    
        With ActiveSheet.PivotTables("PivotTable1")
            For Each c In .PivotFields("Text").DataRange.Cells
                If c.Value = 1 Then
                    With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
                        With .Resize(, .Columns.Count - 1)
                           .Interior.ColorIndex = 6
                        End With
                    End With
                End If
            Next
        End With
    End Sub
     
  14. Hi Jon,

    I read your remarkable articles on this topic and must say I’m impressed with all the information you shared with us! Thank you! It’s being very helpful. However I can’t find any example using more than one row category. Even though my problem is with an externally sourced (MS Access) pivot table, I guess I could manage to solve my problem if I just could know how to determine the range for each row group.

    Take, for example, your pivot table 2, but with a third row category, say “Category 2.1”. Its data could be:

    Nuno's Table

    My goal would be to dynamically highlight the highest (max) value of each data column, but per row category group. In the example, I’d change the cell color of numbers 8 (Alpha,AA,BBB) and 5 (Alpha,BB,CCC) on column “a” plus 9 (Alpha,AA,CCC) and 4 (Alpha,BB,AAA) on “b”. Is a kind of dynamic ranking.

    How can I select each of the four three-number ranges of this example (583, 245, 709 and 412)? Is there an easy way?

    Any help would be much appreciated. Thanks a lot!

    NL

  15. Are “a” and “b” separate items in a pivot field whose label doesn’t appear in your table? Or are they separate pivot fields?

  16. “a” and “b” would be like the ones in your pivot table 2 example, separate items in the pivot field “Category 3”. I haven’t included this label because this text form would make it appear even more complicated than it made. By the way, thanks for the table conversion to a much better format! :-) Just add “Category 3 ” on top of them and you complete my example table.

    I guess there’s a workaround that may work, but would be rather inefficient, probably. That’s using the “intersect” technique you kindly exemplified in the “Referencing Pivot Table Ranges in VBA” thread, in a nested fashion, iterating through all the Cat1/Cat2 visible label pairs. My realife table has some Cat1 values, with about two dozen Cat2 child values, with half to a dozen names under each, roughly. Cat3 is, for example, all the months of the year (12 data columns). The idea is to avoid all the pain to manually apply conditional formatting to every group of names. The final result is a categorized team ranking per month.

    Thank you!!

    NL

  17. I set up this list in Excel 2003 (it would be a table in 2007)

    Nuno's Data

    which produces this pivot table

    Nuno's Pivot Table

    The code does require using Intersect(), but it’s not really inefficient.

    Sub FormatByBlock()
     
    Dim pi1 As PivotItem
    Dim pi2 As PivotItem
    Dim r As Range
    Dim v As Variant
    Dim i1 As Long
    Dim i2 As Long
    Dim vMax As Double
    Dim c As Range
     
    With ActiveSheet.PivotTables("PTformatted")
     
    ' reset default formatting
    With .DataBodyRange
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With
     
    ' loop to get ranges
    For Each pi1 In .PivotFields("Cat2").PivotItems
    For Each pi2 In .PivotFields("Cat3").PivotItems
    Set r = Intersect(pi1.DataRange, pi2.DataRange)
     
    ' find max in range
    v = r.Value
    vMax = v(LBound(v, 1), LBound(v, 2))
    For i1 = LBound(v, 1) To UBound(v, 1)
    For i2 = LBound(v, 2) To UBound(v, 2)
    If v(i1, i2) > vMax Then
    vMax = v(i1, i2)
    End If
    Next
    Next
     
    ' highlight max in range
    For i1 = LBound(v, 1) To UBound(v, 1)
    For i2 = LBound(v, 2) To UBound(v, 2)
    If v(i1, i2) = vMax Then
    With r.Cells(i1 + 1 - LBound(v, 1), i2 + 1 - LBound(v, 2))
    .Font.Bold = True
    .Interior.ColorIndex = 6
    End With
    End If
    Next
    Next
     
    Next
    Next
     
    End With
    End Sub

    Here is the formatted table

    Nuno's Formatted Pivot Table

  18. Jon,

    I have no words for what you just did! You shed some light into my problem, and couldn´t be more responsive and complete than you were. I’m more than grateful.

    Later on I hope to try this code. I’ll need eventually to adapt it, as my table comes from an external Access query. Mainly use “VisibleItems” instead. I’ll post my feedback.

    Huge thanks!

    NL

  19. Hi Jon,

    I had finally the opportunity to evolve on top of your nice code example, adapting the data range selection technique to my data, and I must say it was a really good push towards my goal! What a splendid work you’re doing! Please keep on doing it, sharing with everybody these high quality, not easy to find, bits of information! Thank you!

    As I told you previously, my real world pivot table gets its data from an Access SQL query, forcing me to delve into some additional issues. But overall this problem is solved, thanks to your invaluable help!

    There’s one last problem still unsolved, however. Even though it’s not a “must to have”, any insight would be greatly appreciated. Compact layout tables (Excel 2007) have several advantages that are worth keeping, but with them this code unfortunately doesn’t work. Oddly enough, if we set “Cat2″‘s field settings to “Show item labels in outline form”, the ranges returned by the row selection (pi1) loop revert to empty single row, two column ones. These have empty cells if we don’t use category sub-totals at top row level. The real data ranges aren’t returned at all…

    Any ideas?

    Once again, many thanks!

    NL

  20. Hey Jon,

    Running into another issue and would appreciate any help you can provide! My code was working fine for highlighting an entire row when I had it based on one condition only. When I try to just add in another section for a second condition (wanting the same result, the entire row highlighted) I receive an error (specifically with my End Sub).

    Sub FormatPT1()
    Dim c As Range

    With ActiveSheet.PivotTables(“PivotTable1″)

    ” reset default formatting
    With .TableRange1
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With

    With ActiveSheet.PivotTables(1)
    With Intersect(.PivotFields(“BRAND DESCRIPTION”).PivotItems(“NAME 2”).DataRange.EntireRow, _
    .TableRange1)
    .Font.Bold = True
    .Interior.ColorIndex = 6

    With ActiveSheet.PivotTables(1)
    With Intersect(.PivotFields(“BRAND DESCRIPTION”).PivotItems(“NAME 2”).DataRange.EntireRow, _
    .TableRange1)
    .Font.Bold = True
    .Interior.ColorIndex = 6

    End With
    End With
    End With
    End Sub

  21. There are a few inconsistencies. You reference both of these:

    ActiveSheet.PivotTables(“PivotTable1”)
    ActiveSheet.PivotTables(1)

    The “With ActiveSheet.PivotTables(1)” block appears twice, the second one inside the first.

    I think this will do it:

    Sub FormatPT1()
     
      With ActiveSheet.PivotTables("PivotTable1")
        '' reset default formatting
        With .TableRange1
          .Font.Bold = False
          .Interior.ColorIndex = 0
        End With
    
        With Intersect(.PivotFields("BRAND DESCRIPTION").PivotItems("NAME 2").DataRange.EntireRow, _
            .TableRange1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End With
    End Sub
  22. Hey Jon,

    I made a mistake in my post earlier. I am trying to get the same format for either Name 1 or Name 2 (Sorry! I entered Name 2 twice in my previous post). Is there a way I can include this without running into that same error?

    Thanks again!!

  23. Oh, and as a follow-up, thank you for that catch!! I changed those inconsistencies…

  24. Okay, I ended up getting it to work Jon! Just to follow-up on the blog, please find the code below. Thanks again for all the help!!

    Sub FormatPT1()
    Dim c As Range

    With ActiveSheet.PivotTables(“PivotTable1″)

    ” reset default formatting
    With .TableRange1
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With

    With ActiveSheet.PivotTables(“PivotTable1”)
    With Intersect(.PivotFields(“BRAND DESCRIPTION”).PivotItems(“Name1”).DataRange.EntireRow, _
    .TableRange1)
    .Font.Bold = True
    .Interior.ColorIndex = 6

    End With
    End With
    End With
    End Sub

    Sub FormatPT2()
    Dim c As Range

    With ActiveSheet.PivotTables(“PivotTable1”)
    With Intersect(.PivotFields(“BRAND DESCRIPTION”).PivotItems(“Name2”).DataRange.EntireRow, _
    .TableRange1)
    .Font.Bold = True
    .Interior.ColorIndex = 6

    End With
    End With
    End Sub
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Select Case Target.Name
    Case “PivotTable1”
    FormatPT1
    FormatPT2
    End Select
    End Sub

  25. Steve Wypiszynski says:

    Jon –

    Trying to determine if I’ve uncovered a bug, a bad install of Excell 2007, or operator error.

    I’m using the conditional formatting style for a series of investment returns. However, it seems that the internal calculation for one, two, or three standard is incorrect. In fact, regardless of the number of standard deviations that I select, the same number of cells get formatted. Even stranger, it only seems to occur when looking at observations below the mean.

    In my case, the mean is 2.55% with a standard deviation of .73% But every cell that is .77% below the mean gets highlighted regardless of the number of standard deviations is selected.

    I could write some VBA code to accomplish this, but would rather have the system work as advertised.

  26. Steve –

    You don’t provide the formulas you used in the conditional formatting conditions. I’ve also heard of problems with conditional formatting in 2007. I don’t have enough 2007 CF under my belt to know.

  27. Hi Jon,

    How would you group all the BBs in one group and all the AAs in one group? I need to graph the AAs and BBs in one graph, but AAs side by side in one group and BBs side by side as a second group.

  28. Nina –

    You need to switch the positions of the two fields in the row area of your pivot table, so the one with AA and BB is to the left of the one with Alpha and Beta.

  29. Got it. Thank you!

  30. hi Jon!
    i’d be thankful have help;
    Query:
    I’m working on a pivot table of employee salary and I need different CF on each row against each month. e.g. if it’s January then color changes to blue , if february then it gets yellow etc. …

  31. Elane –

    Using Referencing Pivot Table Ranges in VBA to find the range with the month, you’ll need a loop something like this (warning: untested):

    For Each cMonth in rMonths.Cells
      Select Case cMonth.Value
        Case "Jan"
          iColor = RGB(0,0,255)
        Case "Feb"
          iColor = RGB(255,255,0)
        Cast "etc."
          iColor = RGB(blah,blah,blah)
      End Select
      With Intersect(cMonth.EntireRow, .TableRange1)
        .Interior.Color = iColor   
      End With
    Next
  32. Hello Jon

    Thanks for the great tutorial, it has definitely helped with what I am trying to do. That said I am wondering if it was possible, using this or a similar macro to highlight based on the category name, rather then the value?

    For example, If I was looking to highlight the row based on the Value of BB rather then the value of the BB:a>7 Would I be able to do it?

    I’ve tried modifying your script with the lines as a test

    For Each c In .PivotFields(“Category 2”).DataRange.Cells
    If c.Value >= BB Then

    but get a Debug error 438, “Object doesn’t support this property or method”
    Am I calling the wrong object in this case?

    I’m sorry if this is an obvious answer, as I’m still very new to VBA.

  33. Hi Jon,

    I am using Excel 2007 and I am looking for a way of checking if a cell is part of a pivot table subtotal line, so that I can then do something else. I am using the standard pivot table selection so there are bold lines where the subtotals are, but no actual word ‘total’ like there is if you switch it to the classic view.

    Do you know of anyway in which this can be done?

    I have tried using:

    i = Range(“A5”).PivotCell.PivotCellType

    but it doesn’t pick up anything different on a subtotal line to a normal line.

    Any help would be greatly recieved.

  34. Carly –

    PivotCellType seems broken, but you could try this.

    A cell’s pivotfield is the field it contains the subtotal for. If it’s a subtotal, though, it is not part of the pivot field’s data range. So a cell (“pvtCell”) is part of the subtotal range of a pivot table (“pt”) if

    Intersect(pvtCell, pt.PivotFields(pvtCell.PivotCell.PivotField.Name).DataRange)

    is empty.

  35. Hi Jon,
    Thanks for quick response. I have tried the following, but I must be doing something wrong as it is coming up with an error 91 Object variablt or With Block Variable not set:

    Dim pt As PivotTable

    If Intersect(Range(“B3”), pt.PivotFields(Range(“B3”).PivotCell.PivotField.Name).DataRange) Is Empty Then
    End If

  36. Carly –

    You need to define what pivot table pt refers to.

    Dim pt As PivotTable
    
    On Error Resume Next ' error if range is not in pivot table
    Set pt = Range("B3").PivotTable
    On Error GoTo 0
    
    If Not pt Is Nothing Then ' no error, pt exists
      If Intersect(Range("B3"), _
          pt.PivotFields(Range("B3").PivotCell.PivotField.Name).DataRange) _
          Is Empty Then
        ' formatting code goes here
      End If
    End If
  37. Hi Jon,
    Great tute! I need assistance with formatting a pivot table to match source data. Source data has rows where some account numbers are a different font color because sub-account numbers were created by the user. How can i adapt the code to format font color for pivot items that match account numbers on source data.

    Thank you in advance !

  38. Hi Jon,

    Sorry about the deay in replying, however, I am now getting a different error. My code is this:

    Dim pt As PivotTable

    On Error Resume Next ‘ error if range is not in pivot table

    Set pt = Range(“B3”).PivotTable

    On Error GoTo 0

    If Not pt Is Nothing Then ‘ no error, pt exists

    If Intersect(Range(“B3”), pt.PivotFields(Range(“B3”).PivotCell.PivotField.Name).DataRange) Is Empty Then
    ‘ formatting code goes here
    End If

    End If
    And when I get to the Intersect line I get the following message: Run-time error ‘424’: Object required.

    Any help would be greatly recieved.

  39. Hi John…
    Great tutorial, thank you for enlighting me about these fascinating pivot tables…
    The following code you already posted did it.
    However I like to only select ONE single cell, not the entire row…
    How can I do this and may be iff possible put a special sign like that you find in the conditional formating options you get to see. e.g. a red flag or e.g. a red or green circle…
    Thank you very much I appreciate your job…

    With ActiveSheet.PivotTables(1)
    With Intersect(.PivotFields(“BRAND DESCRIPTION”).PivotItems(“NAME 2″).DataRange.EntireRow, _
    .TableRange1)
    .Font.Bold = True
    .Interior.ColorIndex = 6

  40. The code below is running. However, I would like the comparison to be against another field called SLGoal instead of the static 0.9 that is currently being used because the SLGoal can be different for each row. Is that possible?

    Sub FormatSLByAccount()
    Dim c As Range
    With ActiveSheet.PivotTables(“SLByAccount”)

    ‘ reset default formatting
    With .TableRange1
    .Font.Bold = False
    .Interior.ColorIndex = 0
    End With

    ‘ apply formatting to each row if condition is met
    For Each c In .PivotFields(” Service Level % “).DataRange.Cells
    If c.Value >= 0.9 Then
    With .TableRange1.Rows(c.Row – .TableRange1.Row + 1)
    .Font.Bold = True
    .Interior.ColorIndex = 6
    End With
    End If
    Next
    End With
    End Sub

  41. Thnx for sharing. I pick the solution from your post. :)

  42. Hi Jon –
    Need your help, i am try to do the conditional formatting in Pivot for my below data. I need to color code in
    my databodyrange if it is “Good” then it should be Green and if it is “Bad” it should be Red. Please help me with code..
    Appreciate your help.. please

    Sum of Calls Year WK Booking
    2013
    26 32
    Seller Buyer Good Bad Good Bad
    INDIA US 4 7 1
    UK 5 1 6
    AUS 4 4 1
    FRA 5 2 5 1
    GRE 4 2
    CAN 6 1 5 1
    INDIA Total 28 4 29 4
    Grand Total 28 4 29 4

  43. Hello Jon,

    Thank you for this wonderful article.
    I have three rows, one column and Count Values in my pivot table.
    In my value columns I have percentage format, but I would like to change the subtotal fromats to Text only. Can you help me please?

    Public Sub FormatPT1()
    Dim c As range

    With ActiveSheet.PivotTables(“Details”)

    With Intersect(.PivotFields(“Employee Location”).PivotItems(” “).DataRange.EntireRow, _
    .TableRange1)
    .NumberFormat = “@”
    End With
    End With
    End Sub

Trackbacks

  1. […] Jon Peltier uses VBA to test a condition and apply formatting instead of using Excel's CF Function. Pivot Table Conditional Formatting with VBA | Peltier Tech Blog | Excel Charts I'd be glad to help you adapt either of those examples, or develop alternative code if those don't […]

  2. […] Pivot Table Conditional Formatting with VBA […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0