excel - Trying to copy conditional format to pivottable -

all want copy conditional format each column in source range corresponding columns on pivottable. it's getting closer, i'm getting error 1004 while trying apply format. here's code:

    sub createpivot()         ' define rngtarget , rngsource range type variables         dim rngtarget range         dim rngsource range         dim intlastcol integer         dim intcntrcol integer         dim ws worksheet         dim pt pivottable          set ws = thisworkbook.sheets("sheet2")         ws.cells.clear          ' rngtarget pivottable created (ie: sheet2, cell b3)         set rngtarget = thisworkbook.worksheets("sheet2").range("b3")          ' rngsource defines range used create pivottable         set rngsource = activeworkbook.activesheet.usedrange          ' select range         rngsource.select          ' copy range clipboard         rngsource.copy          ' create new pivottable using rngsource defined above         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          ' error! error when "ws.range(cells(5, intctrcol)).select" first called         intcntrcol = 3 intlastcol             ws.range(cells(5, intctrcol)).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 

this exact same issue in last question, suspected, there little more needs fixed well. if going doing significant excel vba development going forward, should spend time learn patterns can use avoid .select.

alternatively, can select pivot table sheet before modifying it. note this not best practice, allow code run.

add row above ' error! comment:

ws.select 

you misspelled variable name in first row in loop. counter called intcntrcol , refer intctrcol. in habit of compiling code work , you'll never have these kinds of errors. debug -> compile

you can't select range syntax using. instead of

ws.range(cells(5, intctrcol)).select 

you need

ws.cells(5, intcntrcol).select