I have read many, many, many posts on this issue, but didn't find any that help my issue. I have a pivot table that I programatically add/delete datafields to a pivot table. When I add a field I want to do some conditional formatting on it. The problem being I get the dreaded "Application-defined or Object-defined error message on the font.color line. Here is the relevant code
Sub CondFormat(strField As String)
'
' CondFormat Macro
'
'
ActiveSheet.PivotTables("PivotTable6").PivotSelect "'" & strField & "'", _
xlDataAndLabel, True
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961 '<---Code breaks here Error 1004
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
I have tried the selection.formatconditions.delete and changing .color to .colorindex to no avail. I am not sure if it is because I am formatting a calculated field, or if it is because I have renamed the field from it's original name i.e. the original name is "SellMarginDelta" and the new caption is "Margin $ Delta". Although I tried the formatting before changing the Caption with no luck.
Any help would be greatly appreciated.
Bookmarks