waqas
06-08-2015, 11:39 PM
I have below ranges. Its randomly increased or decreased each range have its own color and value. There's merge also. If i use union it merge all together i have to merge max two two cells if it is in range. What i have to do if i click all ranges should select at once changed color put the value in all cells. I am using loops but it take time.
Range("AI77:AJ77,AI78:AJ78,AI79:AJ79,AI80:AJ80,AI81:AJ81,AI82:AJ82,AI83:AJ83,AI84: AJ84,AI85:AJ85,AI86:AJ86,AI87:AJ87,AI88:AJ88,AK86:AL86,AN35:AO35,AN36:AO36"), _
Range("AN43:AO43,AP50,AQ112:AR112,AQ113:AR113,AT103:AU103,AT104:AU104,AT105:AU105, AT106:AU106,AW76:AX76,AW77:AX77,AW78:AX78,AW79:AX79,AW80:AX80,AY34,AY40"), _
Range("AY42,AY43,BL49:BM49,BR58:BS58,BR59:BS59,BR66:BS66,BR67:BS67,BT36:BU36,CF47: CG47,CF48:CG48,CF51:CG51,CF55:CG55,CH52:CI52,CH53:CI53,CH54:CI54"), _
Range("CL36,CR60,CR61,CZ53:DA53,DA34:DB34,DA35:DB35,DA36:DB36,DA37:DB37,DA38:DB38, DM34:DN34,DO40:DP40,DS51:DT51,DW46,DX59:DY59,DX61:DY61"), _
Range("DX62:DY62,DY34,DY36,DY37,DY40,DY42,DY51:DZ51,DY54:DZ54,DZ35,DZ36,EA34:EB34, EA38:EB38,EA41:EB41,EA43:EB43,EA51:EB51"), _
Range("EA65,EB63:EC63,EB64:EC64,EC39,EC40,EE42:EF42,EE51:EF51,EG34,EG60,EI34:EJ34, EI36:EJ36,EI37:EJ37,EI39:EJ39,EI40:EJ40,EI42:EJ42"), _
Range("EI53:EJ53,EL105:EM105,EL106:EM106,EL107:EM107,EL108:EM108,EM38:EN38,EN108:E O108,EN109:EO109,EN110:EO110,EN111:EO111,EN112:EO112,EN113:EO113,EN114:EO11 4,EN115:EO115,EN116:EO116 "), _
Range("EN62:EO62,EN63:EO63,EQ35:ER35,EQ39:ER39,EQ46:ER46,EQ47:ER47,EQ48:ER48,EQ49: ER49,EQ50:ER50,EQ51:ER51,ER58:ES58,ER92,ES123:ET123,ES124,ES52:ET52"), _
Range("ES53:ET53,ES79,ES80,ES81,ES82,ES83,ES84,ES85,ET125,ET62:EU62,EU124:EV124,EV 61:EW61,EY38:EZ38,EZ89,EZ90"), _
Range("EZ91,FB64:FC64,FB65:FC65,FB66:FC66,FB79:FC79,FB80:FC80,FB81:FC81,FB82:FC82, FB83:FC83,FB84:FC84,FB85:FC85,FB86:FC86,FB87:FC87,FB88:FC88,FB89:FC89"), _
Range("FB90:FC90,FB91:FC91,FD62,FE34,FE41,FF58:FG58,FF59:FG59,FG41,FG43,FH102:FI10 2,FH103:FI103,FH104:FI104,FH105:FI105,FH106:FI106,FH80:FI80"), _
Range("FH81:FI81,FJ59,FJ63,FJ64,FJ65,FU30,FX23:FY23,FY16,FY17,FY19,GA37,GB12:GC12, GB13:GC13,GB16:GC16,GB24:GC24"), _
Range("GB58:GC58,GB64:GC64,GB66:GC66,GB67:GC67,GD17,GD34:GE34,GD40:GE40,GD43:GE43, GD62:GE62,GD63:GE63,GE29,GF35,GF36,GF37,GG30:GH30"), _
Range("GG41,GG42,GH10:GI10,GI50:GJ50,GI51:GJ51,GL13:GM13,GL14:GM14,GL43:GM43,GM31: GN31,GN36:GO36,GN39:GO39,GN40:GO40,GO25,GO31,GP34:GQ34"), _
Range("GP59:GQ59,GP64:GQ64,GQ14:GR14,GS16:GT16,GS61,GS66,GS67,GT51,GV42:GW42,GV43: GW43,GV46:GW46,GV49:GW49,GW22,GX52:GY52,GX55:GY55"), _
Range("GX59:GY59,GX61:GY61,GZ52,HB35:HC35,HB37:HC37,HB40:HC40,HB41:HC41,HB46:HC46, HB47:HC47,HB67:HC67,HD52:HE52,HE23:HF23,HF40:HG40,HF46:HG46,HF58:HG58"), _
Range("HG15,HG18,HG26:HH26,HH54,HH61:HI61,HH63:HI63,HH64:HI64,HH65:HI65,HH67:HI67, HI47,HI50,HT15:HU15,HW17,HW18,HX40"), _
Range("HY29:HZ29,HY30:HZ30,IC38:ID38,IC39:ID39,IC40:ID40,ID10,IE29:IF29,IE30:IF30, IE31:IF31,IE41:IF41,IF54:IG54,IH55:II55,IK22:IL22,IK46,IK47"), _
Range("IK48,IL49,IL50,IM28,IN36,IN37,IN40,IN41,IO23:IP23,IO28:IP28,IO29:IP29,IO30: IP30,IP40,IP41,IP42"), _
Range("IP43,IQ10,IQ11,IQ12,IQ13,IQ14,IQ36:IR36,IQ37:IR37,IR49:IS49,IR53:IS53,IR54: IS54,IS17:IT17,IS22,IT58:IU58,IU10:IV10"), _
Range("IU11:IV11,IU12:IV12,IU13:IV13,IU16:IV16,IU17:IV17,IU18:IV18,IU19:IV19,IU51, IV53:IW53,IW58,IY31:IZ31,IZ50:JA50,IZ53:JA53,J37:K37,J38:K38"), _
Range("J39:K39,J40:K40,JA12:JB12,JA30:JB30,JB60:JC60,JC10:JD10,JC11:JD11,JC43,JD22 ,JD23,JD24,JD25,JD26,JD52:JE52,JD55:JE55"), _
Range("JD59:JE59,JF13,JF14,JF60,JF61,JH64:JI64,JI25:JJ25,JI26:JJ26,JI43:JJ43,JJ17, JK18:JL18,JL34,JL35,JL36,JL37"), _
Range("JL40,JL49:JM49,JM11,JM12,JN18,JN48,JN50,JO34,U106:V106,U107:V107,U108:V108, Z40:AA40,Z41:AA41,Z42:AA42,Z43:AA43")).Select
Range("AI77:AJ77,AI78:AJ78,AI79:AJ79,AI80:AJ80,AI81:AJ81,AI82:AJ82,AI83:AJ83,AI84: AJ84,AI85:AJ85,AI86:AJ86,AI87:AJ87,AI88:AJ88,AK86:AL86,AN35:AO35,AN36:AO36"), _
Range("AN43:AO43,AP50,AQ112:AR112,AQ113:AR113,AT103:AU103,AT104:AU104,AT105:AU105, AT106:AU106,AW76:AX76,AW77:AX77,AW78:AX78,AW79:AX79,AW80:AX80,AY34,AY40"), _
Range("AY42,AY43,BL49:BM49,BR58:BS58,BR59:BS59,BR66:BS66,BR67:BS67,BT36:BU36,CF47: CG47,CF48:CG48,CF51:CG51,CF55:CG55,CH52:CI52,CH53:CI53,CH54:CI54"), _
Range("CL36,CR60,CR61,CZ53:DA53,DA34:DB34,DA35:DB35,DA36:DB36,DA37:DB37,DA38:DB38, DM34:DN34,DO40:DP40,DS51:DT51,DW46,DX59:DY59,DX61:DY61"), _
Range("DX62:DY62,DY34,DY36,DY37,DY40,DY42,DY51:DZ51,DY54:DZ54,DZ35,DZ36,EA34:EB34, EA38:EB38,EA41:EB41,EA43:EB43,EA51:EB51"), _
Range("EA65,EB63:EC63,EB64:EC64,EC39,EC40,EE42:EF42,EE51:EF51,EG34,EG60,EI34:EJ34, EI36:EJ36,EI37:EJ37,EI39:EJ39,EI40:EJ40,EI42:EJ42"), _
Range("EI53:EJ53,EL105:EM105,EL106:EM106,EL107:EM107,EL108:EM108,EM38:EN38,EN108:E O108,EN109:EO109,EN110:EO110,EN111:EO111,EN112:EO112,EN113:EO113,EN114:EO11 4,EN115:EO115,EN116:EO116 "), _
Range("EN62:EO62,EN63:EO63,EQ35:ER35,EQ39:ER39,EQ46:ER46,EQ47:ER47,EQ48:ER48,EQ49: ER49,EQ50:ER50,EQ51:ER51,ER58:ES58,ER92,ES123:ET123,ES124,ES52:ET52"), _
Range("ES53:ET53,ES79,ES80,ES81,ES82,ES83,ES84,ES85,ET125,ET62:EU62,EU124:EV124,EV 61:EW61,EY38:EZ38,EZ89,EZ90"), _
Range("EZ91,FB64:FC64,FB65:FC65,FB66:FC66,FB79:FC79,FB80:FC80,FB81:FC81,FB82:FC82, FB83:FC83,FB84:FC84,FB85:FC85,FB86:FC86,FB87:FC87,FB88:FC88,FB89:FC89"), _
Range("FB90:FC90,FB91:FC91,FD62,FE34,FE41,FF58:FG58,FF59:FG59,FG41,FG43,FH102:FI10 2,FH103:FI103,FH104:FI104,FH105:FI105,FH106:FI106,FH80:FI80"), _
Range("FH81:FI81,FJ59,FJ63,FJ64,FJ65,FU30,FX23:FY23,FY16,FY17,FY19,GA37,GB12:GC12, GB13:GC13,GB16:GC16,GB24:GC24"), _
Range("GB58:GC58,GB64:GC64,GB66:GC66,GB67:GC67,GD17,GD34:GE34,GD40:GE40,GD43:GE43, GD62:GE62,GD63:GE63,GE29,GF35,GF36,GF37,GG30:GH30"), _
Range("GG41,GG42,GH10:GI10,GI50:GJ50,GI51:GJ51,GL13:GM13,GL14:GM14,GL43:GM43,GM31: GN31,GN36:GO36,GN39:GO39,GN40:GO40,GO25,GO31,GP34:GQ34"), _
Range("GP59:GQ59,GP64:GQ64,GQ14:GR14,GS16:GT16,GS61,GS66,GS67,GT51,GV42:GW42,GV43: GW43,GV46:GW46,GV49:GW49,GW22,GX52:GY52,GX55:GY55"), _
Range("GX59:GY59,GX61:GY61,GZ52,HB35:HC35,HB37:HC37,HB40:HC40,HB41:HC41,HB46:HC46, HB47:HC47,HB67:HC67,HD52:HE52,HE23:HF23,HF40:HG40,HF46:HG46,HF58:HG58"), _
Range("HG15,HG18,HG26:HH26,HH54,HH61:HI61,HH63:HI63,HH64:HI64,HH65:HI65,HH67:HI67, HI47,HI50,HT15:HU15,HW17,HW18,HX40"), _
Range("HY29:HZ29,HY30:HZ30,IC38:ID38,IC39:ID39,IC40:ID40,ID10,IE29:IF29,IE30:IF30, IE31:IF31,IE41:IF41,IF54:IG54,IH55:II55,IK22:IL22,IK46,IK47"), _
Range("IK48,IL49,IL50,IM28,IN36,IN37,IN40,IN41,IO23:IP23,IO28:IP28,IO29:IP29,IO30: IP30,IP40,IP41,IP42"), _
Range("IP43,IQ10,IQ11,IQ12,IQ13,IQ14,IQ36:IR36,IQ37:IR37,IR49:IS49,IR53:IS53,IR54: IS54,IS17:IT17,IS22,IT58:IU58,IU10:IV10"), _
Range("IU11:IV11,IU12:IV12,IU13:IV13,IU16:IV16,IU17:IV17,IU18:IV18,IU19:IV19,IU51, IV53:IW53,IW58,IY31:IZ31,IZ50:JA50,IZ53:JA53,J37:K37,J38:K38"), _
Range("J39:K39,J40:K40,JA12:JB12,JA30:JB30,JB60:JC60,JC10:JD10,JC11:JD11,JC43,JD22 ,JD23,JD24,JD25,JD26,JD52:JE52,JD55:JE55"), _
Range("JD59:JE59,JF13,JF14,JF60,JF61,JH64:JI64,JI25:JJ25,JI26:JJ26,JI43:JJ43,JJ17, JK18:JL18,JL34,JL35,JL36,JL37"), _
Range("JL40,JL49:JM49,JM11,JM12,JN18,JN48,JN50,JO34,U106:V106,U107:V107,U108:V108, Z40:AA40,Z41:AA41,Z42:AA42,Z43:AA43")).Select