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

Popular posts from this blog

How do you convert a timestamp into a datetime in python with the correct timezone? -

-
you naively expect work: import pytz datetime import datetime def tz_datetime_from_timestamp(timestamp): """convert timestamp datetime """ tz = pytz.timezone('australia/perth') server_time = datetime.utcnow() delta = tz.utcoffset(server_time) value = datetime.utcfromtimestamp(timestamp).replace(tzinfo=tz) return value + delta print tz_datetime_from_timestamp(1416387060) and convert timestamp 1416387060 wed nov 19 16:51:00 2014 gmt+8:00. ...but not. prints: 2014-11-19 16:51:00+07:43 the timezone of australia/perth not gmt+7:43. it gmt+8:00. unequivocally stated on australian government website http://www.australia.gov.au/about-australia/our-country/time : awst equal coordinated universal time plus 8 hours (utc +8). so, heck pytz pulling 7.43 from? well, turns out pytz pulls data http://www.iana.org/time-zones time zone database, , database states: # western australia # # rule name type in on @...
Read more

how to display 2 form fields on same line with bootstrap -

-
i creating form in bootstrap should responsive. see below code. <form novalidate="novalidate" class="form-inline"> <div class="form-group"> <label class="col-sm-4 control-label" for="inputfirstname">first name:</label> <div class="col-sm-4"> <input class="form-control" type="text" id="inputfirstname" ng-model="patient.firstname"/> </div> </div> <div class="form-group"> <label class="col-sm-4 control-label" for="inputlastname">last name:</label> <div class="col-sm-4"> <input class="form-control" type="text" id="inputlastname" ng-model="patient.lastname"/> </div> </div> ...
Read more

c# - how to use buttonedit in devexpress gridcontrol -

-
Image
i trying create gridcontrol buttonedit in 1 of columns. when user clicks button edit, popups form select product. when selection done on popup, returns datarow selection main grid below. but when column loses focus, value wrote column disappear. here code creates gridcontrol's data , buttonedit's click event. private void frmsiparisnew_load(object sender, eventargs e) { dt = new datatable(); dt.columns.add("malzeme_kodu",typeof(string)); dt.columns.add("malzeme_aciklama", typeof(string)); dt.columns.add("adet", typeof(decimal)); dt.columns.add("birim", typeof(string)); dt.columns.add("fiyat", typeof(decimal)); dt.columns.add("kur", typeof(string)); dt.columns.add("tutar", typeof(decimal)); datarow dr = dt.newrow(); dt.rows.add(dr); gc.datasource = dt; } private void repositoryitembuttoneditmal...
Read more