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