Need help with FormatConditions.Add method

Question

When I run the following code snippet:

Sub Test()

Dim i as Integer, j as Integer, CFArray() as Variant

CFArray = Range("CF_Lookup").Value    ' CF_Lookup is a 19 x 2 named range in my workbook

j = UBound(CFArray)

For i = 1 To j

Selection.FormatConditions.Add Type:=xlExpression, Formula1:=CFArray(i, 1)

     Selection.FormatConditions(1).Interior.Color = CFArray(i, 2)

Next i

End Sub

I get a "Run-time error 5: Invalid procedure call or argument" on the first line following the For statement.  In the Watch window, I have confirmed that CFArray(1,1) = "=AND($A3<>1,$D3-$C3>0,H$2>=$C3,H$2<=$D3,TRIM($G3)=""Data Migration Load"")".  It appears that the Formula1 parameter is not recognizing the string contained in CFArray(1,1), but I don't understand why.

Any assistance would be greatly appreciated.  Thanks.

Answer 1

Andreas,

Thanks for your help!  Unfortunately, after revising my code per your code sample, I still got the "Invalid procedure call or argument" error.  On a hunch, I removed all the double double-quotes from all the conditional formatting formulas in CFArray and re-ran my code.  This time everything worked fine!

FYI, below is a sample of one of my conditional formatting formulas:

=AND($A3<>1,$D3-$C3>0,H$2>=$C3,H$2<=$D3,TRIM($G3)=""Data Migration Load"")

The reason for the double double-quotes is because that is what Excel generated when I ran the macro recorder:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A3<>1,$D3-$C3>0,H$2>=$C3,H$2<=$D3,TRIM($G3)=""Data Migration Load"")"

and that is what I inputted in my "CF_Lookup" range (excluding the very first and very last double quotes).  However, I have now learned that when you exclude the very first and very last double quotes (i.e., the one before "=" and the one after ")" ), the double double-quotes are also unnecessary.

Thanks again for your help.  I greatly appreciate it.

Looks like you figured out the answer.

Answer 2

To the Moderator of this forum, could you kindly mark my Question as Answered?  I ended up discovering the answer to my own question.

Thanks.

Answer 3

Andreas,

Thanks for your help!  Unfortunately, after revising my code per your code sample, I still got the "Invalid procedure call or argument" error.  On a hunch, I removed all the double double-quotes from all the conditional formatting formulas in CFArray and re-ran my code.  This time everything worked fine!

FYI, below is a sample of one of my conditional formatting formulas:

=AND($A3<>1,$D3-$C3>0,H$2>=$C3,H$2<=$D3,TRIM($G3)=""Data Migration Load"")

The reason for the double double-quotes is because that is what Excel generated when I ran the macro recorder:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A3<>1,$D3-$C3>0,H$2>=$C3,H$2<=$D3,TRIM($G3)=""Data Migration Load"")"

and that is what I inputted in my "CF_Lookup" range (excluding the very first and very last double quotes).  However, I have now learned that when you exclude the very first and very last double quotes (i.e., the one before "=" and the one after ")" ), the double double-quotes are also unnecessary.

Thanks again for your help.  I greatly appreciate it.

Answer 4

I suggest that you write the formula in your area and you read from there. This will prevent that it contains an error. And the formula must be in your local language.

Andreas.

Sub Test()

  Dim i As Long, CFArray() As Variant

  If Not TypeOf Selection Is Range Then Exit Sub

  CFArray = Range("CF_Lookup").FormulaLocal

  With Selection

    For i = 1 To UBound(CFArray)

      .FormatConditions.Add Type:=xlExpression, Formula1:=CFArray(i, 1)

      .FormatConditions(1).Interior.Color = CFArray(i, 2)

    Next

  End With

End Sub