Run time Error "1004"

[Dsunil05]

****demo.xlsm file link
https://drive.google.com/open?id=0B1u8QGSQIxdoeXRvRHd2THVaenM



following code is working only for 28 Ranges which is 15849 cells (highlight in red color) but not running with all ranges that past below in brown color
[h=2]Find same value and Changing a cell & tex color WITHOUT using Conditional Formatting[/h]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long, LastRow As Long, DataRange As Range, DR As Range
Set DataRange = Range(" A2:T28,V2:AO28,AQ2:BJ28,BL2:CE28,CG2:CZ28,DB2:DU28,DW2:EP28,ER2:FK28,FM2:GF28,GH2:HA28,HC2:HV28,HX2:IQ28,IS2:JL28,JN2:KG 28,KI2:LB28,LD2:LW28,LY2:MR28,MT2:NM28,NO2:OH28,OJ2:PC28,PE2:PX28,PZ2:QS28,QU2:RN28,RP2:SI28,SK2:TD28,TF2:TY28,UA2:UT28, UV2:VO28")
If Not Intersect(Target, DataRange) Is Nothing Then
Application.ReplaceFormat.Clear
For Each DR In DataRange.Areas
LastRow = Split(DR.Address, "$")(4)
DR.Resize(LastRow - DR.Row).Interior.Color = xlNone
DR.Resize(LastRow - DR.Row).Font.Color = vbBlack
For C = DR.Column To DR.Column + DR.Columns.Count - 1
Application.ReplaceFormat.Interior.Color = Cells(LastRow, C).Interior.Color
Application.ReplaceFormat.Font.Color = Cells(LastRow, C).Font.Color
DR.Resize(LastRow - DR.Row).Replace Cells(LastRow, C), "", xlWhole, SearchFormat:=False, ReplaceFormat:=True
Next
Next
End If
End Sub


Set DataRange = Range("A2:T28, V2:AO28, AQ2:BJ28, BL2:CE28, CG2:CZ28, DB2:DU28, DW2:EP28, ER2:FK28, FM2:GF28, GH2:HA28, HC2:HV28, HX2:IQ28, IS2:JL28, JN2:KG28, KI2:LB28, LD2:LW28, LY2:MR28, MT2:NM28, NO2:OH28, OJ2:PC28, PE2:PX28, PZ2:QS28, QU2:RN28, RP2:SI28, SK2:TD28, TF2:TY28, UA2:UT28, UV2:VO28, VQ2:WJ28, WL2:XE28, XG2:XZ28, YB2:YU28, YW2:ZP28, ZR2:AAK28, AAM2:ABF28, ABH2:ACA28, ACC2:ACV28, ACX2:ADQ28, ADS2:AEL28, AEN2:AFG28, AFI2:AGB28, AGD2:AGW28, AGY2:AHR28, AHT2:AIM28, AIO2:AJH28, AJJ2:AKC28, AKE2:AKX28, AKZ2:ALS28, ALU2:AMN28, AMP2:ANI28, ANK2:AOD28, AOF2:AOY28, APA2:APT28, APV2:AQO28, AQQ2:ARJ28, ARL2:ASE28, ASG2:ASZ28, ATB2:ATU28, ATW2:AUP28, AUR2:AVK28, AVM2:AWF28, AWH2:AXA28, AXC2:AXV28, AXX2:AYQ28, AYS2:AZL28, AZN2:BAG28, BAI2:BBB28, BBD2:BBW28, BBY2:BCR28, BCT2:BDM28, BDO2:BEH28, BEJ2:BFC28, BFE2:BFX28, BFZ2:BGS28, BGU2:BHN28, BHP2:BII28, BIK2:BJD28, BJF2:BJY28, B2:CBS28, CBU2:CCN28, CCP2:CDI28, CDK2:CED28, CEF2:CEY28, CFA2:CFT28, CFV2:CGO28, CGQ2:CHJ28, CHL2:CIE28, CIG2:CIZ28, CJB2:CJU28, CJW2:CKP28, CKR2:CLK28, CLM2:CMF28, CMH2:CNA28, CNC2:CNV28, CNX2:COQ28, COS2:CPL28, CPN2:CQG28, CQI2:CRB28, CRD2:CRW28, CRY2:CSR28, CST2:CTM28, CTO2:CUH28, CUJ2:CVC28, CVE2:CVX28, CVZ2:CWS28, CWU2:CXN28, CXP2:CYI28, CYK2:CZD28, CZF2:CZY28, DAA2:DAT28, DAV2:DBO28, DBQ2:DCJ28, DCL2:DDE28, DDG2:DDZ28, DEB2:DEU28, DEW2:DFP28, DFR2:DGK28, DGM2:DHF28, DHH2:DIA28, DIC2:DIV28, DIX2:DJQ28, DJS2:DKL28, DKN2:DLG28, DLI2:DMB28, DMD2:DMW28, DMY2:DNR28, DNT2:DOM28, DOO2:DPH28, DPJ2:DQC28, DQE2:DQX28, DQZ2:DRS28, DRU2:DSN28, DSP2:DTI28, DTK2:DUD28, DUF2:DUY28, DVA2:DVT28, DVV2:DWO28, DWQ2:DXJ28, DXL2:DYE28, DYG2:DYZ28, DZB2:DZU28, DZW2:EAP28, EAR2:EBK28, EBM2:ECF28, ECH2:EDA28, EDC2:EDV28, EDX2:EEQ28, EES2:EFL28, EFN2:EGG28, EGI2:EHB28, EHD2:EHW28, EHY2:EYV28, EYX2:EZQ28, EZS2:FAL28, FAN2:FBG28, FBI2:FCB28, FCD2:FCW28, FCY2:FDR28, FDT2:FEM28, FEO2:FFH28, FFJ2:FGC28, FGE2:FGX28, FGZ2:FHS28, FHU2:FIN28, FIP2:FJI28, FJK2:FKD28, FKF2:FKY28, FLA2:FLT28, FLV2:FMO28, FMQ2:FNJ28, FNL2:FOE28, FOG2:FOZ28, FPB2:FPU28, FPW2:FQP28, FQR2:FRK28, FRM2:FSF28, FSH2:FTA28, FTC2:FTV28, FTX2:FUQ28, FUS2:FVL28, FVN2:FWG28, FWI2:FXB28, FXD2:FXW28, FXY2:FYR28, FYT2:FZM28, FZO2:GAH28, GAJ2:GBC28, GBE2:GBX28, GBZ2:GCS28, GCU2:GDN28, GDP2:GEI28, GEK2:GFD28, GFF2:GFY28, GGA2:GGT28, GGV2:GHO28, GHQ2:GIJ28, GIL2:GJE28, GJG2:GJZ28, GKB2:GKU28, GKW2:GLP28, GLR2:GMK28, GMM2:GNF28, GNH2:GOA28, GOC2:GOV28, GOX2:GPQ28, GPS2:GQL28, GQN2:GRG28, GRI2:GSB28, GSD2:GSW28, GSY2:GTR28, GTT2:GUM28, GUO2:GVH28, GVJ2:GWC28, GWE2:GWX28, GWZ2:GXS28, GXU2:GYN28, GYP2:GZI28, GZK2:HAD28, HAF2:HAY28, HBA2:HBT28, HBV2:HCO28, HCQ2:HDJ28, HDL2:HEE28, HEG2:HEZ28, HFB2:HVY28, HWA2:HWT28, HWV2:HXO28, HXQ2:HYJ28, HYL2:HZE28, HZG2:HZZ28, IAB2:IAU28, IAW2:IBP28, IBR2:ICK28, ICM2:IDF28, IDH2:IEA28, IEC2:IEV28, IEX2:IFQ28, IFS2:IGL28, IGN2:IHG28, IHI2:IIB28, IID2:IIW28, IIY2:IJR28, IJT2:IKM28, IKO2:ILH28, ILJ2:IMC28, IME2:IMX28, IMZ2:INS28, INU2:ION28, IOP2:IPI28, IPK2:IQD28, IQF2:IQY28, IRA2:IRT28, IRV2:ISO28, ISQ2:ITJ28, ITL2:IUE28, IUG2:IUZ28, IVB2:IVU28, IVW2:IWP28, IWR2:IXK28, IXM2:IYF28, IYH2:IZA28, IZC2:IZV28, IZX2:JAQ28, JAS2:JBL28, JBN2:JCG28, JCI2:JDB28, JDD2:JDW28, JDY2:JER28, JET2:JFM28, JFO2:JGH28, JGJ2:JHC28, JHE2:JHX28, JHZ2:JIS28, JIU2:JJN28, JJP2:JKI28, JKK2:JLD28, JLF2:JLY28, JMA2:JMT28, JMV2:JNO28, JNQ2:JOJ28, JOL2:JPE28, JPG2:JPZ28, JQB2:JQU28, JQW2:JRP28, JRR2:JSK28, JSM2:JTF28, JTH2:JUA28, JUC2:JUV28, JUX2:JVQ28, JVS2:JWL28, JWN2:JXG28, JXI2:JYB28, JYD2:JYW28, JYY2:JZR28, JZT2:KAM28, KAO2:KBH28, KBJ2:KCC28, KCE2:KTB28, KTD2:KTW28, KTY2:KUR28, KUT2:KVM28, KVO2:KWH28, KWJ2:KXC28, KXE2:KXX28, KXZ2:KYS28, KYU2:KZN28, KZP2:LAI28, LAK2:LBD28, LBF2:LBY28, LCA2:LCT28, LCV2:LDO28, LDQ2:LEJ28, LEL2:LFE28, LFG2:LFZ28, LGB2:LGU28, LGW2:LHP28, LHR2:LIK28, LIM2:LJF28, LJH2:LKA28, LKC2:LKV28, LKX2:LLQ28, LLS2:LML28, LMN2:LNG28, LNI2:LOB28, LOD2:LOW28, LOY2:LPR28, LPT2:LQM28, LQO2:LRH28, LRJ2:LSC28, LSE2:LSX28, LSZ2:LTS28, LTU2:LUN28, LUP2:LVI28, LVK2:LWD28, LWF2:LWY28, LXA2:LXT28, LXV2:LYO28, LYQ2:LZJ28, LZL2:MAE28, MAG2:MAZ28, MBB2:MBU28, MBW2:MCP28, MCR2:MDK28, MDM2:MEF28, MEH2:MFA28, MFC2:MFV28, MFX2:MGQ28, MGS2:MHL28, MHN2:MIG28, MII2:MJB28, MJD2:MJW28, MJY2:MKR28, MKT2:MLM28, MLO2:MMH28, MMJ2:MNC28, MNE2:MNX28, MNZ2:MOS28, MOU2:MPN28, MPP2:MQI28, MQK2:MRD28, MRF2:MRY28, MSA2:MST28, MSV2:MTO28, MTQ2:MUJ28, MUL2:MVE28, MVG2:MVZ28, MWB2:MWU28, MWW2:MXP28, MXR2:MYK28, MYM2:MZF28, MZH2:NQE28, NQG2:NQZ28, NRB2:NRU28, NRW2:NSP28, NSR2:NTK28, NTM2:NUF28, NUH2:NVA28, NVC2:NVV28, NVX2:NWQ28, NWS2:NXL28, NXN2:NYG28, NYI2:NZB28, NZD2:NZW28, NZY2:OAR28, OAT2:OBM28, OBO2:OCH28, OCJ2:ODC28, ODE2:ODX28, ODZ2:OES28, OEU2:OFN28, OFP2:OGI28, OGK2:OHD28, OHF2:OHY28, OIA2:OIT28, OIV2:OJO28, OJQ2:OKJ28, OKL2:OLE28, OLG2:OLZ28, OMB2:OMU28, OMW2:ONP28, ONR2:OOK28, OOM2:OPF28, OPH2:OQA28, OQC2:OQV28, OQX2:ORQ28, ORS2:OSL28, OSN2:OTG28, OTI2:OUB28, OUD2:OUW28, OUY2:OVR28, OVT2:OWM28, OWO2:OXH28, OXJ2:OYC28, OYE2:OYX28, OYZ2:OZS28, OZU2:PAN28, PAP2:PBI28, PBK2:PCD28, PCF2:PCY28, PDA2:PDT28, PDV2:PEO28, PEQ2:PFJ28, PFL2:PGE28, PGG2:PGZ28, PHB2:PHU28, PHW2:PIP28, PIR2:PJK28, PJM2:PKF28, PKH2:PLA28, PLC2:PLV28, PLX2:PMQ28, PMS2:PNL28, PNN2:POG28, POI2:PPB28, PPD2:PPW28, PPY2:PQR28, PQT2:PRM28, PRO2:PSH28, PSJ2:PTC28, PTE2:PTX28, PTZ2:PUS28, PUU2:PVN28, PVP2:PWI28, PWK2:QNH28, QNJ2:QOC28, QOE2:QOX28, QOZ2:QPS28, QPU2:QQN28, QQP2:QRI28, QRK2:QSD28, QSF2:QSY28, QTA2:QTT28, QTV2:QUO28, QUQ2:QVJ28, QVL2:QWE28, QWG2:QWZ28, QXB2:QXU28, QXW2:QYP28, QYR2:QZK28, QZM2:RAF28, RAH2:RBA28, RBC2:RBV28, RBX2:RCQ28, RCS2:RDL28, RDN2:REG28, REI2:RFB28, RFD2:RFW28, RFY2:RGR28, RGT2:RHM28, RHO2:RIH28, RIJ2:RJC28, RJE2:RJX28, RJZ2:RKS28, RKU2:RLN28, RLP2:RMI28, RMK2:RND28, RNF2:RNY28, ROA2:ROT28, ROV2:RPO28, RPQ2:RQJ28, RQL2:RRE28, RRG2:RRZ28, RSB2:RSU28, RSW2:RTP28, RTR2:RUK28, RUM2:RVF28, RVH2:RWA28, RWC2:RWV28, RWX2:RXQ28, RXS2:RYL28, RYN2:RZG28, RZI2:SAB28, SAD2:SAW28, SAY2:SBR28, SBT2:SCM28, SCO2:SDH28, SDJ2:SEC28, SEE2:SEX28, SEZ2:SFS28, SFU2:SGN28, SGP2:SHI28, SHK2:SID28, SIF2:SIY28, SJA2:SJT28, SJV2:SKO28, SKQ2:SLJ28, SLL2:SME28, SMG2:SMZ28, SNB2:SNU28, SNW2:SOP28, SOR2:SPK28, SPM2:SQF28, SQH2:SRA28, SRC2:SRV28, SRX2:SSQ28, SSS2:STL28, STN2:TKK28, TKM2:TLF28, TLH2:TMA28, TMC2:TMV28, TMX2:TNQ28, TNS2:TOL28, TON2:TPG28, TPI2:TQB28, TQD2:TQW28, TQY2:TRR28, TRT2:TSM28, TSO2:TTH28, TTJ2:TUC28, TUE2:TUX28, TUZ2:TVS28, TVU2:TWN28, TWP2:TXI28, TXK2:TYD28, TYF2:TYY28, TZA2:TZT28, TZV2:UAO28, UAQ2:UBJ28, UBL2:UCE28, UCG2:UCZ28, UDB2:UDU28, UDW2:UEP28, UER2:UFK28, UFM2:UGF28, UGH2:UHA28, UHC2:UHV28, UHX2:UIQ28, UIS2:UJL28, UJN2:UKG28, UKI2:ULB28, ULD2:ULW28, ULY2:UMR28, UMT2:UNM28, UNO2:UOH28, UOJ2:UPC28, UPE2:UPX28, UPZ2:UQS28, UQU2:URN28, URP2:USI28, USK2:UTD28, UTF2:UTY28, UUA2:UUT28, UUV2:UVO28, UVQ2:UWJ28, UWL2:UXE28, UXG2:UXZ28, UYB2:UYU28, UYW2:UZP28, UZR2:VAK28, VAM2:VBF28, VBH2:VCA28, VCC2:VCV28, VCX2:VDQ28, VDS2:VEL28, VEN2:VFG28, VFI2:VGB28, VGD2:VGW28, VGY2:VHR28, VHT2:VIM28, VIO2:VJH28, VJJ2:VKC28, VKE2:VKX28, VKZ2:VLS28, VLU2:VMN28, VMP2:VNI28, VNK2:VOD28, VOF2:VOY28, VPA2:VPT28, VPV2:VQO28, VQQ2:WHN28, WHP2:WII28, WIK2:WJD28, WJF2:WJY28, WKA2:WKT28, WKV2:WLO28, WLQ2:WMJ28, WML2:WNE28, WNG2:WNZ28, WOB2:WOU28, WOW2:WPP28, WPR2:WQK28, WQM2:WRF28, WRH2:WSA28, WSC2:WSV28, WSX2:WTQ28, WTS2:WUL28, WUN2:WVG28, WVI2:WWB28, WWD2:WWW28, WWY2:WXR28, WXT2:WYM28, WYO2:WZH28, WZJ2:XAC28, XAE2:XAX28, XAZ2:XBS28, XBU2:XCN28, XCP2:XDI28, XDK2:XED28, XEF2:XEY28")



Now, What to do?

Reply Please

 

[taurean]

As you must have realized that it is due to limitation of passing string for setting range.

What is the reason that you cannot use:

Set DataRange = Range("A2:XEY28")

 

[Dsunil05]

Code:
Set DataRange = Range("A2:XEY28")

its working smoothly but its showing all duplicates or same number in that worksheet not particular set e.g. A2:T28
kindly use .xlsm file link and check what i do in sheet1(view code)






data range A2:T28 it is first set of numbers then next set e.g V2:AO28, AQ2:BJ28, BL2:CE28, CG2:CZ28, DB2:DU28,........,XEF2:XEY28
in every set i want to find out separately
same number And change their "cell color,text color and font style bold " in data range A2:T28 of row no 28's only 20 number only in colour.
Without using Conditional Formatting
In MS Excel 2007
Thanks

 

[taurean]

Not sure if I have understood you correctly. Test this code and see if it gives desired results.

Right click on sheet tab and choose "View Code" and paste it there.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, C As Long, LastRow As Long, DataRange As Range, DR As Range
Application.ReplaceFormat.Clear


i = 1
Do While i <= Range("XEY28").Column
    Set DR = Cells(2, i).Resize(27, 20)
    If Not Intersect(DR, Target) Is Nothing Then 'Remove this if statement if you need all columns
        LastRow = Split(DR.Address, "$")(4)
        DR.Resize(LastRow - DR.Row).Interior.Color = xlNone
        DR.Resize(LastRow - DR.Row).Font.Color = vbBlack
        For C = DR.Column To DR.Column + DR.Columns.Count - 1
          Application.ReplaceFormat.Interior.Color = Cells(LastRow, C).Interior.Color
          Application.ReplaceFormat.Font.Color = Cells(LastRow, C).Font.Color
          DR.Resize(LastRow - DR.Row).Replace Cells(LastRow, C), "", xlWhole, SearchFormat:=False, ReplaceFormat:=True
        Next
    End If                                       'Remove this if statement if you need all columns
    i = i + 21
Loop


Application.ReplaceFormat.Clear
End Sub

 

[Dsunil05]

Thanks,
my problem is solved