Conditional formatting ok manual, not ok vba

[Mark Easter]

Using Excel 2003, I have a conditional formatting problem where the exact same formatting works when entered manually but does not work when entered using vba. However, the vba formatting works after simply opening the Conditional Formatting dialog and clicking OK without making any changes.

The vba code fragment follows ...

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNT(IF($I$2:$I$2307="""",IF($L$2:$L$2307<>""noscript"",IF($E$2:$E$2307<9000,1,IF($E$2:$E$2307>9999,1,"""")),""""),""""))>0"
        Selection.FormatConditions(1).Interior.ColorIndex = 22

Is this a bug? Is there a way around it?

 

[kpark91]

First replace any """" to "" then see if it works.

 

[Mark Easter]

With """"" replaced with "" ...

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNT(IF($I$2:$I$2307="",IF($L$2:$L$2307<>""noscript"",IF($E$2:$E$2307<9000,1,IF($E$2:$E$2307>9999,1,""),""),""))>0"

Produces Run Time Error 5, Invalid procedure call or argument

 

[Mark Easter]

Found a solution ... removed all the trailing false condition null strings ...

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNT(IF($I$2:$I$2307="""",IF($L$2:$L$2307<>""noscript"",IF($E$2:$E$2307<9000,1,IF($E$2:$E$2307>9999,1)))))>0"

Thanks for the assist kpark1.