2007 Pivot Table Conditional Formatting macro problem

[BBM]

Hi All,

I have the following code in my sheet, however, only column "3" gets conditional formatting, not the second column ("11").

Does anyone know why?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

...
UpdateConditionalFormatting

...
End Sub



Sub UpdateConditionalFormatting()
Dim Rng As Range
' Update Column 3
Set Rng = ActiveSheet.PivotTables("PivotTable1").DataBodyRange.Offset(0, 3).Columns(1)

Rng.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=0.3"
Rng.FormatConditions(1).SetFirstPriority
Rng.FormatConditions(1).Font.Color = -16776961
Rng.FormatConditions(1).StopIfTrue = True


Rng.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=0.8"
Rng.FormatConditions(2).SetFirstPriority
Rng.FormatConditions(2).Font.Color = -11489280
Rng.FormatConditions(2).StopIfTrue = False


'Update Column 11
Set Rng = ActiveSheet.PivotTables("PivotTable1").DataBodyRange.Offset(0, 11).Columns(1)

Rng.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=0.3"
Rng.FormatConditions(1).SetFirstPriority
Rng.FormatConditions(1).Font.Color = -16776961
Rng.FormatConditions(1).StopIfTrue = True


Rng.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=0.8"
Rng.FormatConditions(2).SetFirstPriority
Rng.FormatConditions(2).Font.Color = -11489280
Rng.FormatConditions(2).StopIfTrue = False

End Sub

[Bob Phillips]

Shouldn't you be passing the offset index to the procedure?

[BBM]

Yes your right... but it doesnt make any difference to the (non) outcome :o(

[Bob Phillips]

You mean this makes no difference?



Sub UpdateConditionalFormatting(ByVal idx As Long)
Dim Rng As Range
' Update Column 3
Set Rng = ActiveSheet.PivotTables("PivotTable1").DataBodyRange.Offset(0, idx).Columns(1)

[BBM]

Yeah ....

UpdateConditionalFormatting(3) works
UpdateConditionalFormatting(11) doesnt work!

[Bob Phillips]

Can you post a workbook we can try it on, with the code.