Excel VBA - Using .ModifyAppliesTo and Run-Time Error 1004 (Problem Trying To Insert A String That Is Too Long?)

Question

I am very new to coding with Excel VBA and am currently stuck trying to get Excel to conditionally format my desired cells based on a formula. I am using the property .ModifyAppliesToRange. My code used works fine if I use a short example string such as "D5,D6,D7,D8". What I want to do is insert a much bigger string such as:

"=D5,N5,X5,AH5,J6,T6,AD6,F7,P7,Z7,AJ7,L8,V8,AF8,H9,R9,AB9,D10,N10,X10,AH10,J11,T11,AD11,F12,P12,Z12,AJ12,L13,V13,AF13,H14,R14,AB14,D15,N15,X15,AH15,J16,T16,AD16,F17,P17,Z17,AJ17,L18,V18,AF18,H19,R19,AB19,D20,N20,X20,AH20,J21,T21,AD21,F22,P22,Z22,AJ22,L23,V23,AF23,H24,R24,AB24,D25,N25,X25,AH25,J26,T26,AD26,F27,P27,Z27,AJ27,L28,V28,AF28,H29,R29,AB29,D30,N30,X30,AH30,J31,T31,AD31,F32,P32,Z32,AJ32,L33,V33,AF33,H34,R34,AB34,D35,N35,X35,AH35,J36,T36,AD36,F37,P37,Z37,AJ37,L38,V38,AF38,H39,R39,AB39,D40,N40,X40,AH40,J41,T41,AD41,F42,P42,Z42,AJ42,L43,V43,AF43,H44,R44,AB44,D45,N45,X45,AH45,J46,T46,AD46,F47,P47,Z47,AJ47,L48,V48,AF48,H49,R49,AB49,D50,N50,X50,AH50,J51,T51,AD51,F52,P52,Z52,AJ52,L53,V53,AF53,H54,R54,AB54"

However, when the string gets longer in length the program crashes and I get the following error:

"Run-time error 1004: Method 'Range of object'_Global failed.

Below is my code (apologies it is only the last few lines that are directly relevant):

'This code does the conditional formatting for the target spreadsheet!
Dim targetFile As String
Dim targetSheet As String
Dim targetTestCell As String
Dim targetTestSheet As String
Dim targetTestWorkbook As String
Dim formulaAddMe As String
Dim exampleInsert As String

targetFile = Range("D20").Value
targetSheet = Range("D21").Value
firstCell = Range("D17") & Range("D18")
targetTestCell = Range("AD13").Value
targetTestWorkbook = Range("C32").Value
targetTestSheet = Range("C33").Value

    Windows(targetFile).Activate
    Sheets(targetSheet).Select
    Range(firstCell).Select

formulaAddMe = "=[" & targetTestWorkbook & "]" & targetTestSheet & "!" & targetTestCell & "=0"



    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        formulaAddMe
    Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    exampleInsert = "D5,D6,D7,D8" 'this is a variable I used to check my code works - the example code works as this string is short in length
    MsgBox exampleInsert
    With Selection.FormatConditions(1)
    .ModifyAppliesToRange Range(exampleInsert)
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

After doing some experimentation and gradually making the exampleInsert string longer the .ModifyAppliesTo works until the point at which the characters for V23 are added.

exampleInsert = "=D5,N5,X5,AH5,J6,T6,AD6,F7,P7,Z7,AJ7,L8,V8,AF8,H9,R9,AB9,D10,N10,X10,AH10,J11,T11,AD11,F12,P12,Z12,AJ12,L13,V13,AF13,H14,R14,AB14,D15,N15,X15,AH15,J16,T16,AD16,F17,P17,Z17,AJ17,L18,V18,AF18,H19,R19,AB19,D20,N20,X20,AH20,J21,T21,AD21,F22,P22,Z22,AJ22,L23,V23"

So summarising to me it looks like the problem is the program is not inserting strings that are above a certain length.

Can someone please explain what do I need to do to fix my problem? Thank you!

Answer 1

Try This...

Edited your code. Range() does not accept string of more than 255 characters. The string is split into individual range addresses into an array and then Union of those ranges is made with a loop through the array.

Sub ConditionalFormatAppl()
Dim targetFile As String
Dim targetSheet As String
Dim targetTestCell As String
Dim targetTestSheet As String
Dim targetTestWorkbook As String
Dim formulaAddMe As String
Dim exampleInsert As String

targetFile = Range("D20").Value
targetSheet = Range("D21").Value
firstCell = Range("D17") & Range("D18")
targetTestCell = Range("AD13").Value
targetTestWorkbook = Range("C32").Value
targetTestSheet = Range("C33").Value

    Windows(targetFile).Activate
    Sheets(targetSheet).Select
    Range(firstCell).Select

formulaAddMe = "=[" & targetTestWorkbook & "]" & targetTestSheet & "!" & targetTestCell & "=0"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        formulaAddMe
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    exampleInsert = "D5,N5,X5,AH5,J6,T6,AD6,F7,P7,Z7,AJ7,L8,V8,AF8,H9,R9,AB9,D10,N10," & _
            "X10,AH10,J11,T11,AD11,F12,P12,Z12,AJ12,L13,V13,AF13,H14,R14,AB14,D15,N15," & _
            "X15,AH15,J16,T16,AD16,F17,P17,Z17,AJ17,L18,V18,AF18,H19,R19,AB19,D20,N20," & _
            "X20,AH20,J21,T21,AD21,F22,P22,Z22,AJ22,L23,V23,AF23,H24,R24,AB24,D25,N25," & _
            "X25,AH25,J26,T26,AD26,F27,P27,Z27,AJ27,L28,V28,AF28,H29,R29,AB29,D30,N30," & _
            "X30,AH30,J31,T31,AD31,F32,P32,Z32,AJ32,L33,V33,AF33,H34,R34,AB34,D35,N35," & _
            "X35,AH35,J36,T36,AD36,F37,P37,Z37,AJ37,L38,V38,AF38,H39,R39,AB39,D40,N40," & _
            "X40,AH40,J41,T41,AD41,F42,P42,Z42,AJ42,L43,V43,AF43,H44,R44,AB44,D45,N45," & _
            "X45,AH45,J46,T46,AD46,F47,P47,Z47,AJ47,L48,V48,AF48,H49,R49,AB49,D50,N50," & _
            "X50,AH50,J51,T51,AD51,F52,P52,Z52,AJ52,L53,V53,AF53,H54,R54,AB54"
    
'<<<< THIS BLOCK IS ADDED NEW START >>>>
    Dim ApplyRng As Range, i As Long, ApplyRngArr
    ApplyRngArr = Split(exampleInsert, ",")
    For i = 0 To UBound(ApplyRngArr)
        If ApplyRng Is Nothing Then
        Set ApplyRng = Range(ApplyRngArr(i))
        Else
        Set ApplyRng = Union(ApplyRng, Range(ApplyRngArr(i)))
        End If
    Next i
'<<<< THIS BLOCK IS ADDED NEW END >>>>
        
    MsgBox exampleInsert
    With Selection.FormatConditions(1)
    .ModifyAppliesToRange ApplyRng 'Resulting range of the above Union Loop
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub