VBA Error 1004 Object Range failed for _Global

Question

I need to make a little VBA Application for a School project.

I recorded a Macro which Resize all Cells and then make them green. After that I select specific Cells and recolor them in white. So the result should be the Excel logo. However when I run the code there is an Error 1004 Range Object failed for _Global.

Code:

Sub Resize()

Columns("A:BZ").ColumnWidth = 2.71
Rows("1:1000").RowHeight = 15
Cells.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 4485149
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Union(Range( _
    "O14:P15,L7:T7,S5:T6,P6:Q6,E38:F47,G46:J47,G42:J43,G38:J39,N38:O39,O40:R41,R38:S39,P42:Q43,O44:R45,N46:O47,R46:S47,W38:X47,Y46:AB47,Y38:AB39,AF38:AK39,AF40:AG47,AH46:AK47,AH42:AK43,AO38:AP47,AQ46:AT47,R6,Y7:AP9,AN10:AP31,Y29:AM31,AF10:AF28,AG24:AM24,Y24:AE24" _
    ), Range( _
    "AG19:AM19,AG14:AM14,Y14:AE14,V4:X33,U5:U32,T12:T25,S14:S23,R16:R21,Q18:Q19,Q28:T32,M26:R27,N24:Q25,O22:P23,L28:P31,H28:K30,F9:G29,H8:J27,K12:K25,L14:L23,M16:M21,N18:N19,K8:T9,M10:R11,N12:Q13" _
    )).Select
Cells.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
End Sub

You can step through your code line by line in the vbexplorer by pressing F8 can you try that? — Preston
In which line is happening the error? We probably cannot "send you a complete solution" but we can help you to reach your solution. — Ricardo Pontual
Check your Ranges inside your Union line. Try defining a Dim UnionRng As Range then set it to Set UnionRng = Union(Range("O14:P15,L7:T7,S5:T6"), Range("P6:Q6,E38:F47,G46:J47")) you'll see it's just fine. So it's something inside your Ranges, maybe they are overlapping or something — Shai Rado

user3598756 user3598756 · Accepted Answer · 2016-12-09T10:40:31

the string fed to Range must be less then 256 characters, while your first range has just 257... so just shift some characters to the 2nd range

furthermore you're selecting all cells instead of wanted ones

see code:

Option Explicit

Sub Resize()

    With Range("A1:BZ1000")
        .ColumnWidth = 2.71
        .RowHeight = 15
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 4485149
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    With Union(Range( _
        "O14:P15,L7:T7,S5:T6,P6:Q6,E38:F47,G46:J47,G42:J43,G38:J39,N38:O39,O40:R41,R38:S39,P42:Q43,O44:R45,N46:O47,R46:S47,W38:X47,Y46:AB47,Y38:AB39,AF38:AK39,AF40:AG47,AH46:AK47,AH42:AK43,AO38:AP47,AQ46:AT47,R6,Y7:AP9,AN10:AP31,Y29:AM31" _
        ), Range( _
        "AF10:AF28,AG24:AM24,Y24:AE24,AG19:AM19,AG14:AM14,Y14:AE14,V4:X33,U5:U32,T12:T25,S14:S23,R16:R21,Q18:Q19,Q28:T32,M26:R27,N24:Q25,O22:P23,L28:P31,H28:K30,F9:G29,H8:J27,K12:K25,L14:L23,M16:M21,N18:N19,K8:T9,M10:R11,N12:Q13" _
        )).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

VBA Error 1004 Object Range failed for _Global

1 Answers