Error when using VBA to apply condition formats on Excel Sheet (from Access)

Matt_g

New member
Local time
Today, 20:27
Joined
Oct 14, 2014
Messages
6
Error when using VBA to apply conditional formats on Excel Sheet (from Access)

Good Morning.

I am pushing some data to Excel from an Access query. When the data is in Excel I reformat the sheet by changing the fonts, applying borders and cell formats - I have got all of this to work fine.

The one thing I am struggling with is applying conditional formats. I am pretty sure it is something to do with incorrectly referencing the applcation/sheet. An extract of what i think to be the key parts of the code are below. Could anyone help with where I am going wrong?

The whole sub is pretty lengthy but i can paste it if needed.

Any help would be appreciated.


....

Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object

...

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets("Sheet1")

...
'intI is a count of the number of rows

xlWSh.Range("I1:AT" & intI).FormatConditions.Delete
xlWSh.Range("I3:I" & intI).Select
ApXL.Application.CutCopyMode = False
ApXL.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I$2=1"
ApXL.Selection.FormatConditions(xlWSh.Selection.FormatConditions.Count).SetFirstPriority
With ApXL.Selection.FormatConditions(1).Font
.Color = -1003520
.TintAndShade = 0
End With
With ApXL.Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
ApXL.Selection.FormatConditions(1).StopIfTrue = False
 
Last edited:
Hi Matt,

What exactly is 'going wrong'? Do you get an error or does the code run, but does not apply the CF as you would expect?

Al
 
Hi - It stops running the sub and I get an error in access saying "object doesn't support this property or method".

The first 2 lines run ok - it is when it gets to the formatconditions part that it errors.
 
On which line does that error occur?
 
It errors on this line i think:

ApXL.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I$2=1"
 
Okay here's one thought

You are using late-binding (that is to say, you are not referencing the Excel object library, but using generic Object variables and setting the Excel application object and subsequent workbook / worksheet objects as you go)

However, you then use constants which are inherent to the Excel object library (xlExpression, xlAutomatic), and which therefore require this reference to be present in order to compile and run (early-binding)

You need to pick one or the other - you can either early-bind, or late-bind, not both!

Try replacing "xlExpression" and "xlAutomatic" with the enumerated versions (they should be 2 and -4105 respectively - have highlighted the changes below)

Code:
xlWSh.Range("I1:AT" & intI).FormatConditions.Delete
xlWSh.Range("I3:I" & intI).Select
ApXL.Application.CutCopyMode = False
ApXL.Selection.FormatConditions.Add [COLOR=red]Type:=2[/COLOR], Formula1:="=$I$2=1"
ApXL.Selection.FormatConditions(xlWSh.Selection.FormatConditions.Count).SetFirstPriority
With ApXL.Selection.FormatConditions(1).Font
  .Color = -1003520
  .TintAndShade = 0
End With
With ApXL.Selection.FormatConditions(1).Interior
[COLOR=red]  .PatternColorIndex = -4105[/COLOR]
  .Color = 15773696
  .TintAndShade = 0
End With
ApXL.Selection.FormatConditions(1).StopIfTrue = False

Just a tip - always put "Option Explicit" at the top of any module or class component code - it will detect problems like these very, very quickly!

Here's a handy link to get the enumerated versions of Excel constants as well. A better idea, though, would be to use early-binding when you are developing your VBA, and then switch to late-binding when you are happy that it works.
 
Thank you so much - that makes perfect sense and it now works.

It has been bugging me for 3 days now!
 
Well, look on the bright side, you won't make that mistake again!

Glad to hear you have it sorted now - that Option Explicit at the top will save you so much time trawling through code trying to identify bad variables.

Best of luck with the rest of it!
 
And thank you for the list of excel constants too.

They will come in handy will copying macros that have been recorded in excel.
 

Users who are viewing this thread

Back
Top Bottom