Excel Conditional Formatting expanding with pivot tables?

Question

Once conditional formatting is applied to a pivot table, if you expand the table, how can the conditional formatting be updated automatically to apply itself to the new expanded pivot table?

Attached is the sample excel file.

Answer 1

The following is verbatim from my post at http://yoursumbuddy.com/re-apply-excel-pivot-table-conditional-formatting/. You might also be interested in http://yoursumbuddy.com/unified-method-of-pivot-table-formatting/:

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.

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

Answer 2

Pivot tables are notoriously unresponsive to formatting and have a history of not keeping manually applied formatting, not even mentioning extending conditional formatting. If you require that,you may want to consider using VBA to re-apply formats after a refresh, or apply conditional formats to whole rows or whole columns.

Answer 3

I'm using 2010 so i can't say this would work in other versions, also as far as i have tested this only works if you want the conditional formatting to apply to all cells within the pivot chart (not just one specific row of data, you may be able to exclude grand totals, but i haven't tried).

However before doing any coding it might be worth a quick try if you want the formatting to apply to all rows in your table.

Create a New Worksheet > Enter your conditional formatting into a cell in this new worksheet > use the copy formatting paint brush on this cell > go back to your pivot chart worksheet and drag the formatting over first top left hand value down to the last bottom left value.

Refresh your chart, expand collapse your chart, your formatting should stay put, not only that, but even when you remove all the fields from your pivot and add them back in, stays in place. it seems that by doing this it applies the formatting to the chart it'self rather than the individual cells within in.

I found this out quite by accident but as I am often on here looking for answers it seemed only right to let people know what i discovered, even if it was a fluke.