excel - PivotTable FormatConditions ScopeType is causing 1004 -

this question exact duplicate of:

• conditional formatting pivottable report 1 answer

a 1004 error occurs @ end of sub, when trying set scopetype. want formatcondition apply active rows in current column, thought it.

    sub createpivot()         ' define rngtarget , rngsource range type variables         dim rngtarget range         dim rngsource range         dim intlastcol integer         dim intcntrcol integer         dim ws1, ws2 worksheet         dim pt pivottable         dim cf formatcondition          set ws1 = thisworkbook.sheets("sheet1")         set ws2 = thisworkbook.sheets("sheet2")         ws2.cells.clear          ' rngtarget pivottable created (ie: sheet2, cell b3)         set rngtarget = ws2.range("b3")         'set rngtarget = thisworkbook.worksheets("sheet2").range("b3")          ' rngsource defines range used create pivottable         ' activeworkbook = opened workbook         ' activesheet = currectly opened sheet         ' usedrange = range of cells active data in them         set rngsource = ws1.usedrange         'set rngsource = activeworkbook.activesheet.usedrange          ' select range         ws1.select         rngsource.select          ' copy range clipboard         rngsource.copy          ' create new pivottable using rngsource defined above,         ' in excel format,         ' placed @ rngtarget location,         ' , name pivotb3 reference if needed         activeworkbook.pivotcaches.create(xldatabase, rngsource).createpivottable rngtarget, "pivotb3"         set pt = rngtarget.pivottable          ' last used column data table         intlastcol = rngsource.columns(rngsource.columns.count).column          ' select pivot table can apply conditional formats         pt.pivotselect "", xldataandlabel, true          intcntrcol = 3 intlastcol             ws2.select             ws2.cells(4, intcntrcol).select ' select current sum column             selection.formatconditions.add type:=xlcellvalue, operator:=xlless, formula1:="=5000" ' set conditional format less 5000             selection.formatconditions(selection.formatconditions.count).setfirstpriority ' take priority on other formats             selection.formatconditions(1).font ' use font property next operations                 .themecolor = xlthemecolorlight1 ' set default (if not meet condition)                 .tintandshade = 0 ' same above             end             selection.formatconditions(1).interior                 .patterncolorindex = xlautomatic                 .color = 65535 ' set background color yellow                 .tintandshade = 0             end             selection.formatconditions(1).stopiftrue = false             selection.formatconditions(1).scopetype = xlfieldsscope ' apply format rows match "sum of xxxx"         next intcntrcol     end sub 

based on last question propose method applying formatting:

set ws2 = thisworkbook.sheets("sheet2") ws2.usedrange     .formatconditions.add type:=xlcellvalue, operator:=xlless, formula1:="=5000" ' set conditional format less 5000     .formatconditions(.formatconditions.count).setfirstpriority ' take priority on other formats     .formatconditions(1).font ' use font property next operations         .themecolor = xlthemecolorlight1 ' set default (if not meet condition)         .tintandshade = 0 ' same above     end     .formatconditions(1).interior         .patterncolorindex = xlautomatic         .color = 65535 ' set background color yellow         .tintandshade = 0     end     .formatconditions(1).stopiftrue = false end