Excel Conditional Formatting Loop -



Excel Conditional Formatting Loop -

i have code:

selection.formatconditions.add type:=xlexpression, formula1:= _ "=if($b5=""arc"",1,0)" selection.formatconditions(selection.formatconditions.count).setfirstpriority selection.formatconditions(1).interior .patterncolorindex = xlautomatic .color = colorsheet.range("arc_color").interior.color .tintandshade = 0 end selection.formatconditions(1).stopiftrue = false selection.formatconditions.add type:=xlexpression, formula1:= _ "=if($b5=""all"",1,0)" selection.formatconditions(selection.formatconditions.count).setfirstpriority selection.formatconditions(1).interior .patterncolorindex = xlautomatic .color = colorsheet.range("all_color").interior.color .tintandshade = 0 end selection.formatconditions(1).stopiftrue = false

there more blocks , changes named ranges referenced within conditional formatting conditions.

=if($b5=""all"",1,0) colorsheet.range("all_color").interior.color

what best way loop this? don't have 10 blocks of code same?

can alter read named ranges off of list on separate sheet?

thanks

create procedure this, loops through range on separate worksheet, called "variables" in example. range contains 10 changing values:

edit: modfified per @killersnail's prepare in comments, , added parameter pass colorsheet.

sub loopcf() dim wsvariables excel.worksheet dim colorsheet excel.worksheet dim cell excel.range set wsvariables = thisworkbook.worksheets("variables") set colorsheet = activesheet ' alter suit needs 'this range contains "all", "arc", etc. each cell in wsvariables.range("a2:a11") applycf cell.value2 next cell end sub

then phone call existing code loop, passing "arc", "all" , other variables.

sub applycf(wscolorsheet excel.worksheet, conditiontocheck string) selection.formatconditions.add type:=xlexpression, formula1:= _ "=if($b5=" & chr(34) & conditiontocheck & chr(34) & ",1,0)" selection.formatconditions(selection.formatconditions.count).setfirstpriority selection.formatconditions(1).interior .patterncolorindex = xlautomatic .color = wscolorsheet.range(conditiontocheck & "_color").interior.color .tintandshade = 0 end selection.formatconditions(1).stopiftrue = false end sub

loops excel-vba conditional-formatting

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more