I often use conditional formatting in pivot tables, often to add banding to detail rows and highlights to total rows. I like conditional formatting in XL 2010 for the most part, but sometimes it’s persnickety. It seems to change its mind from day-to-day about what’s allowed.
One well-known problem is that if you apply conditional formatting to both your row fields and the data items, like this:
and then refresh it, the formatting is wiped from the data (values) area, as shown below:
There are a couple of ways to fix this. One is to specifically apply the formats to the values area(s), a new feature as of Excel 2007. Conditional formats added this way aren’t cleared by pivot table refreshes:
This works fairly well as long as your data area only includes one values field, but if you are pivoting on multiple values fields, you’ll have to add the rule for each one. And you can’t specify row fields in this dialog, so you’ll have define the formats again for those areas. And if you alter the formats you’ll have to do it all again.
For these reasons I’d rather just apply the conditional formatting to the row headings and the values area in one fell swoop. But I don’t want to visit the condtional formatting dialog to re-expand the range each time a pivot table is refreshed.
So, I wrote the code below to expand the condtional formatting from the first row label cell into all the row label and data area cells:
Sub Extend_Pivot_CF_To_Data_Area()
Dim pvtTable As Excel.PivotTable
Dim rngTarget As Excel.Range
Dim rngSource As Excel.Range
Dim i As Long
'check for inapplicable situations
If ActiveSheet Is Nothing Then
MsgBox ("No active worksheet.")
Exit Sub
End If
On Error Resume Next
Set pvtTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
If Err.Number = 1004 Then
MsgBox "The cursor needs to be in a pivot table"
Exit Sub
End If
On Error GoTo 0
With pvtTable
'format conditions will be applied to row headers and values areas
Set rngTarget = Intersect(.DataBodyRange.EntireRow, .TableRange1)
'set the format condition's source to the first cell in the row area
Set rngSource = rngTarget.Cells(1)
With rngSource.FormatConditions
For i = 1 To .Count
'reset each format condition's range to row header and values areas
.Item(i).ModifyAppliesToRange rngTarget
Next i
End With
'display isn't always refreshed otherwise
Application.ScreenUpdating = True
End With
End Sub
The key to this code is the ModifyAppliesToRange method of each FormatCondtion. This code identifies the first cell of the row label range and loops through each format condition in that cell and re-applies it to the range formed by the intersection of the row label range and the values range, i.e., the banded area in the first image above.
This method relies on all the conditional formatting you want to re-apply being in that first row labels cell. In cases where the conditional formatting might not apply to the leftmost row label, I’ve still applied it to that column, but modified the condition to check which column it’s in.
This function can be modified and called from a SheetPivotTableUpdate event, so when users or code updates a pivot table it re-applies automatically. I’ve also added this macro to the Pivot Table Context Menu and some days it gets used a lot.
