+ Reply to Thread
Results 1 to 2 of 2

Pivot Table Conditional Formatting VBA Issue

Hybrid View

cyberwolf Pivot Table Conditional... 04-26-2012, 12:26 PM
cyberwolf Re: Pivot Table Conditional... 04-26-2012, 01:10 PM
  1. #1
    Registered User
    Join Date
    06-03-2011
    Location
    Pennsylvania USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Pivot Table Conditional Formatting VBA Issue

    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.

  2. #2
    Registered User
    Join Date
    06-03-2011
    Location
    Pennsylvania USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Pivot Table Conditional Formatting VBA Issue

    I figured it out. I had to change "XlDataAndLabel" to "xlDataOnly" and it works fine.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1