Invalid procedure call or argument applying Excel conditional formatting from Access vba

Question

I have read numerous examples where this code works; however, when I try to execute it, it isn't working and I don't see why.

Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Range("G2:G375").FormatConditions.Add Type:=xlExpression, Formula1:="=OR(G2=""New"",G2=""Reintroduced"")"

I get a Run-time error '5': Invalid procedure call or argument. Have tried it by defining activesheet, worksheet name, active workbook, etc. and still get this same error. Everything I have read says it should work, but again, it isn't.

Also, there is data in the spreadsheet that is opened else where in the code.

Here is just one example of what I am using as a reference:

enter link description here

Answer 1

You may need to add a reference to the Microsoft Excel Object Library in Access.

With that reference, this code worked when I tested it.

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Add
xl.ActiveWorkbook.ActiveSheet.Range("G2:G375").FormatConditions.Add Type:=xlExpression, Formula1:="=OR(G2=""New"",G2=""Reintroduced"")"
xl.Visible = True

Answer 2

I had the same problem recently with formulas when I was working on another laptop. Found out the the formula was wrong, as excel was set up for "," and the formula contained ";" as a separator. Classic.