Excel 2010 - Conditionally Format Pivot Table

[geekette]

Hi

Since being upgraded at work to Excel 2010 I've discovered that conditional formatting on a pivot table no longer works.....and I have some existing workbooks where I need to find a work around...

Through research I have found and then tweaked the following code which works....however....it then seems to get caught in a loop somehow and the workbook freezes and I am forced to close it.

Can anyone suggest/point me in the direction of where I'm going wrong?


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Select Case Target.Name
Case "PivotTables1"
ConditionalFormat_Red
End
End Select

End Sub


Sub ConditionalFormat_Red()

Dim StaffedTime As Range
Dim Lunch As Range
Dim TotalAbsence As Range
Dim MeasuredHours As Range
Dim All As Range

Application.ScreenUpdating = False

'First set all formatting to 0

Range("F10:I300").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

'Format Staffed Time Column
Call FormatRedRow(StaffedTime, Range("F10:F500"), 9)

'Format Lunch column
Call FormatRedRow(Lunch, Range("G10:G500"), 8)

'Format TotalAbsence column
Call FormatRedRow(TotalAbsence, Range("H10:H500"), 7)

'Format MeasuredHours column
Call FormatRedRow(MeasuredHours, Range("I10:I500"), 6)

Range("A1").Select

End Sub

Function FormatRedRow(RangeName As Range, rng As Range, OffsetRef As Integer)

For Each RangeName In rng
If RangeName.Offset(0, OffsetRef).Value = 1 Then
With RangeName.Interior
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399975585192419
End With
End If
Next RangeName

End Function