BBM
10-05-2009, 07:17 AM
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
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