excel - With VBA how to apply conditional formatting to the last column of a pivot table according specific cell values -
i have below pivot table :
risk impact high low medium grand total aa 2 7 3 12 ab 10 10 ac 2 1 2 5 ad 2 1 3 ae 1 1 ba 3 1 4 bb bc 1 1
and apply specific cell colour according value of each cells describe below:
- no risk findings : "grand total cell colour" = green
- 1 2 low risk findings; or 1 medium risk finding : "grand total cell colour" = orange
- as of more 2 low risk findings; or of 1 low risk finding , 1 medium risk finding;or of more 1 medium risk finding; or of 1 high risk finding : "grand total cell colour" = red
the approach tried define each "pivot item" , apply if statement assess each conditions.
please find code below:
sub conditionalformatting4() dim c range activesheet.pivottables("pivottable2") set = activesheet.pivottables("pivottable2").pivotfields("risk impact").pivotitems("high").datarange.cells set u = activesheet.pivottables("pivottable2").pivotfields("risk impact").pivotitems("low").datarange.cells set v = activesheet.pivottables("pivottable2").pivotfields("risk impact").pivotitems("medium").datarange.cells set c = activesheet.pivottables("pivottable2").pivotfields("risk impact").pivotitems("grand total").datarange.cells ' apply formatting last column cell if condition met each c in .pivotfields("risk impact").pivotitems("grand total").datarange.cells if i.value >= 1 or v.value > 1 or u.value > 2 or (v.value >= 1 , u.value >= 1) .tablerange1.rows(c.row - .tablerange1.row + 1) .font.bold = true .interior.colorindex = 3 end elseif (u.value > 1 , u.value < 2) or v.value = 1 .tablerange1.rows(c.row - .tablerange1.row + 1) .font.bold = true .interior.colorindex = 45 end else .tablerange1.rows(c.row - .tablerange1.row + 1) .font.bold = true .interior.colorindex = 4 end end if next end end sub
so know if think correct approach?
then error "grand total column" not recognised. ideas?
Comments
Post a Comment