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
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: