Applying Conditional Format to PivotTable Returning Error 1004: Order of Conditions

msmays · Feb 21st 2017

Hi everyone,

I'm running into a strange quirk. I am reading the conditional format of a cell and then applying it to my PivotTable. Here is the code:

Code

With PvtTbl
            If .DataFields.Count = 0 Then GoTo ExitSub                                                                  'Exit if no facts are selected
            .DataBodyRange.FormatConditions.Delete                                                                     'Delete old conditional formatting
            For Each PvtFld In .DataFields                                                                                     'Loop through all selected facts
                With PvtFld.DataRange
                    intR = Application.Match(Trim(PvtFld.Caption), Worksheets("Number Format").Columns(1), 0)         'Get row number of fact on Number Format worksheet
                    If Worksheets("Number Format").Range("C" & intR).FormatConditions.Count > 0 Then                    'If fact has conditional formatting on Number Format worksheet
                        Worksheets("Number Format").Range("C" & intR).Copy                                                         'Copy cell format needed for fact
                        Worksheets("PivotTable").Cells(.Row, .Column).PasteSpecial xlPasteFormats                             'Paste format into PivotTable
                        For intFormCond = 1 To Worksheets("Number Format").Range("C" & intR).FormatConditions.Count 'Loop through FormatConditions
                            With .FormatConditions(intFormCond)
                                .ScopeType = xlDataFieldScope                                                           'XXXXX Apply conditional formatting to PivotTable XXXXXXX
                                .StopIfTrue = True
                            End With
                        Next intFormCond
                    End If
                End With
            Next PvtFld
End With

Display More

The line

Code

.ScopeType = xlDataFieldScope 'XXXXX Apply conditional formatting to PivotTable XXXXXXX

is where I sometimes run into an error. Thus far, it seems like when there are two conditions, it works. When there are three conditions, it only works if the conditions are in a certain order. Any ideas on why this might be happening or how I can get around it?

Thanks!

msmays · Feb 23rd 2017

Re: Applying Conditional Format to PivotTable Returning Error 1004: Order of Conditio

Although I am still not sure why this is happening, I've changed the code so that I can avoid the issue entirely. Hopefully if anyone ever comes across a similar issue, they can use this to help.

Instead of pasting the formats to one cell and then applying to the entire DataField using ScopeType, I instead pasted the format to all cells in the DataField. Since I am calling this macro each time the PivotTable changes, it will have the same impact.

Code

With PvtFld.DataRange[INDENT]intR = Application.Match(Trim(PvtFld.Caption), Worksheets("Number Format").Columns(1), 0)           'Get row number of fact on Number Format worksheet[/INDENT]
          If Worksheets("Number Format").Range("C" & intR).FormatConditions.Count > 0 Then                    'If fact has conditional formatting on Number Format worksheet
              Worksheets("Number Format").Range("C" & intR).Copy                                              'Copy cell format needed for fact
              PvtFld.DataRange.PasteSpecial xlPasteFormats                                                    'Paste format into PivotTable
         End If
End With

Applying Conditional Format to PivotTable Returning Error 1004: Order of Conditions

Participate now!

Share