When Using Selection.FormatConditions.Add – how do you provide the file name given to Formula1 so that Excel does not crash?

I have a program that conditionally formats cells of another workbook. This evening, for the first time, I noticed the program started crashing with the message “Run-time error 5: Invalid procedure call or argument”. I believe the reason for this is due to the name of the file that I am trying to conditionally format contains brackets(!) I write this because when I save the file with the brackets removed and run my program again, there is no Run-time error 5 issue! Can anyone please explain if there is a way of overcoming the brackets issue?

Thank you!

My code is as follows:

formulaAddMe = "=[" & targetTestWorkbook & "]" & targetTestSheet & "!" & targetPlacementPosition & formulaAddMe3

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                    formulaAddMe
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                With Selection.FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    .Color = colourToInsert
                End With

When the file name contains brackets – in my case it is PixelActivitySimpsons6(MeNewRandomPixels).xlsx - the variable formulaAddMe contains the following:

formulaAddMe is
= [PixelActivitySimpsons6(MeNewRandomPixels).xlsx]Sheet2!$C$4=1

When the file name does not have brackets – so it becomes PixelActivitySimpsons6MeNewRandomPixels.xlsx - the variable formulaAddMe contains the following:

formulaAddMe is
= [PixelActivitySimpsons6MeNewRandomPixels.xlsx]Sheet2!$C$4= 1

Can anyone please explain if there is a way of formatting my formulaAddMe so that it can be processed successfully in the Selection.FormatConditions.Add?

Thank you!

Solution

Conditional formatting doesn't allow formulas to reference a different workbook.

On this general issue of parentheses in filenames used formulas, you can fix that by surrounding the file/sheet in ''

Eg:

='[PixelActivitySimpsons6(MeNewRandomPixels).xlsx]Sheet2'!$C$4=1