Pivot Table Conditional Formatting with VBA

by Jon Peltier
Wednesday, October 21st, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

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.

Bookmark and share this entry:

Posted: Wednesday, October 21st, 2009 under VBA.
Comments: 26

Comments

Comment from Arturas
Time: Wednesday, October 21, 2009, 3:17 am

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

Comment from Sebastien Labonne
Time: Wednesday, October 21, 2009, 11:47 am

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.

Comment from LEM
Time: Thursday, October 22, 2009, 10:10 am

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!!

Comment from Jon Peltier
Time: Thursday, October 22, 2009, 10:28 am

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

Comment from Colin Banfield
Time: Thursday, October 22, 2009, 11:21 am

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.

Comment from Jon Peltier
Time: Thursday, October 22, 2009, 11:38 am

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.)

Comment from Colin Banfield
Time: Thursday, October 22, 2009, 1:41 pm

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.

Comment from Jon Peltier
Time: Thursday, October 22, 2009, 9:50 pm

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.

Comment from LEM
Time: Thursday, October 22, 2009, 10:56 pm

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!!

Comment from Jon Peltier
Time: Thursday, October 22, 2009, 11:46 pm

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

Comment from LEM
Time: Monday, October 26, 2009, 10:41 am

Jon! It is working! Thank you for all of the help!!!

Comment from Shanna Shaw
Time: Friday, October 30, 2009, 9:24 am

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

Comment from Jon Peltier
Time: Friday, October 30, 2009, 9:42 am

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

Comment from Nuno Leal
Time: Friday, January 15, 2010, 1:46 pm

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!

Comment from Jon Peltier
Time: Friday, January 15, 2010, 2:38 pm

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

Comment from Nuno Leal
Time: Saturday, January 16, 2010, 12:06 pm

“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!!

Comment from Jon Peltier
Time: Saturday, January 16, 2010, 12:53 pm

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 WithEnd Sub

Here is the formatted table

Nuno's Formatted Pivot Table

Comment from Nuno Leal
Time: Saturday, January 16, 2010, 1:56 pm

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!

Comment from Nuno Leal
Time: Sunday, January 17, 2010, 8:18 pm

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!

Comment from LEM
Time: Tuesday, February 9, 2010, 9:04 am

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

End With
End With
End With
End Sub

Comment from Jon Peltier
Time: Tuesday, February 9, 2010, 10:28 am

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

Comment from LEM
Time: Tuesday, February 9, 2010, 10:33 am

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!!

Comment from LEM
Time: Tuesday, February 9, 2010, 10:35 am

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

Comment from LEM
Time: Tuesday, February 9, 2010, 1:26 pm

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

Comment from Steve Wypiszynski
Time: Tuesday, February 16, 2010, 3:54 pm

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.

Comment from Jon Peltier
Time: Tuesday, February 16, 2010, 8:33 pm

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.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Main menu:

Peltier Tech
Chart Add-Ins

Excel Dashboards

Subscribe

Site search

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories