CODEWITHSUNDEEP
excelvbams-accessconditional-formatting
cheslijones
cheslijones

Reputation: 9194

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

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

Upvotes: 0

Views: 1901

Answers (2)

cbm9000
cbm9000

Reputation: 61

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.

Upvotes: 0

Jonathan
Jonathan

Reputation: 663

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

Upvotes: 3

Related Questions