Let’s take another journey to the nexus of conditional formatting and pivot tables. My latest foray involved adding per-item color-scales to a pivot table, something like this, where the color scale is applied separately to each quarter:
You can see, for example, that the very high sales of $9,999,999 for January in Region 1 affect the color scale for the other cells in Quarter 1. However, the color scale within other quarters are unaffected, and a 99,999 value in Quarter 2 gets the same green that the much higher value did in Quarter 1.
Here’s another view with each month getting its own scale:
Finally, this shows icon sets by region. Notice that although they span each region for all months, the icon sets ignore the subtotals, which is often preferable:
My code allows you to include or include or ignore subtotals in each item’s color scale. I went about this a couple of ways before settling on the PivotTable.PivotSelect method. I learned about this method recently in a comment by Mike Alexander on one of Jeff Weir’s massive DDOE posts. I can’t find any good documentation, but in Excel 2007 forward you can do things like:
Select a pivot table’s Row Grand Total area
Select the data rows for the East PivotItem of the Region PivotField
(There is a PivotSelect method in 2003, but these types of statements failed.)
It looks like PivotSelect is the VBA version of clicking, for example, a single subtotal in a pivot table and thereby selecting them all. I figured out the syntax by turning on the Macro Recorder and doing those types of selections:
I’m not crazy about using PivotSelect – I assume all that selecting will slow things down on big pivot tables. On the other hand it seems to work consistently. I had much more complicated code that worked most of the time, but only most.
Here’s the code, which runs in Excel 2007 on:
Dim pvt As Excel.PivotTable
Dim pvtItem As Excel.PivotItem
Dim pvtField As Excel.PivotField
Dim rngTest As Excel.Range
'Lots of selecting!
Application.ScreenUpdating = False
Set pvt = pvtFieldToFormat.Parent
With pvt
For Each pvtItem In pvtFieldToFormat.PivotItems
'A distressing number of possible errors from hidden items.
'If the item has no DataRange ignore it.
On Error Resume Next
Set rngTest = pvtItem.DataRange
If Err.Number = 0 Then
'Specify the Field name in case there are multiple items
'with the same name,for example from Grouping.
'Surround PivotItem names with single quotes in case they have spaces.
pvt.PivotSelect pvtFieldToFormat.Name & "['" & pvtItem.Name & "';Data;Total]", xlDataOnly
'Call routine that does the formatting
FormatRange Selection
End If
On Error GoTo 0
Next pvtItem
If IgnoreSubtotals Then
'Clear the FormatConditions from Row and Column fields
For Each pvtField In pvt.PivotFields
If pvtField.Orientation = xlRowField Or pvtField.Orientation = xlColumnField Then
'I can't figure a test for whether the subtotals are visible
'so just blindly firing away.
On Error Resume Next
.PivotSelect "'" & pvtField.Name & "'[All;Total]", xlDataOnly
If Err.Number = 0 Then
Selection.FormatConditions.Delete
End If
On Error GoTo 0
End If
Next pvtField
.PivotSelect "'Row Grand Total'", xlDataOnly
Selection.FormatConditions.Delete
.PivotSelect "'Column Grand Total'", xlDataOnly
Selection.FormatConditions.Delete
Else
End If
End With
Application.ScreenUpdating = True
End Sub
The FormatRange routine is just Macro Recorder code generated by selecting part of the pivot table and applying a color scale. You can do the same thing for data bars or icons.
In the sample workbook, the code is triggered by a WorkSheet_Change event that fires whenever one of the three yellow data validation cells is changed.
Pingback: Excel Roundup 20131216 | Contextures Blog