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.
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.
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.
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.
Here is PivotTable2 with the same formatting applied.
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.
Arturas says
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
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.
LEM says
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!!
Jon Peltier says
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
Colin Banfield says
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.
Jon Peltier says
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.)
Colin Banfield says
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.
Jon Peltier says
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.
LEM says
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!!
Jon Peltier says
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
LEM says
Jon! It is working! Thank you for all of the help!!!
Shanna Shaw says
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
Jon Peltier says
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
Nuno Leal says
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:
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
Jon Peltier says
Are “a” and “b” separate items in a pivot field whose label doesn’t appear in your table? Or are they separate pivot fields?
Nuno Leal says
“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
Jon Peltier says
I set up this list in Excel 2003 (it would be a table in 2007)
which produces this pivot table
The code does require using Intersect(), but it’s not really inefficient.
Here is the formatted table
Nuno Leal says
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
Nuno Leal says
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
LEM says
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
Jon Peltier says
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:
LEM says
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!!
LEM says
Oh, and as a follow-up, thank you for that catch!! I changed those inconsistencies…
LEM says
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
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.
Jon Peltier says
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.
Nina says
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.
Jon Peltier says
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.
Nina says
Got it. Thank you!
Elane says
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. …
Jon Peltier says
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):
Alvin says
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.
Carly Bond says
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.
Jon Peltier says
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.
Carly Bond says
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
Jon Peltier says
Carly –
You need to define what pivot table pt refers to.
christian says
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 !
Carly Bond says
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.
antonio says
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
Brad says
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
NN says
Thnx for sharing. I pick the solution from your post. :)
matthoma says
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
Nil says
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
Cristian says
Hello!
I have a pivot table and its cells are red. The cells outside the pivot table are purple.
When I decrease the size of the table the cells from the outside become white.
Can you give me a code to color cells from the outside with purple when the pivot table is resizing?
Eduardo Pereira Garcia says
I did not use the whole code, but it was a great reference to solve my issue!
Thank you