Struggling with something that should be obvious. I'm trying to open, format and save an Excel spreadsheet in the background from Access. Everything's working fine other than the conditional formatting lines. They don't return an error but they simply do not work for no reason I can figure :banghead:
What am I doing wrong?
The same two lines work fine if pasted into a module in the actual spreadsheet, but that is not what I want to do, for various reasons.
Thanks
What am I doing wrong?
Code:
Dim Excelsheet As Object
Set Excelsheet = CreateObject("Excel.Application")
With Excelsheet
.Visible = False
.Workbooks.Open FileName:= "C:\My_Workbook.xls"
.Columns("B:D").NumberFormat = "£#,##0.00" ' Currency format columns B-D
.Range("A1:D1").Font.Bold = True ' Bold A1-D1 (title bar)
.Range("A1:D1").Interior.Color = 65535 ' Yellow title bar.
'Conditional format column B: this is the bit that is just simply being ignored!
[B] .Columns("B:B").FormatConditions.Add Type:=1, Operator:=4, Formula1:="=0" ' xlCellValue, xlNotEqual, 0
.Columns("B:B").FormatConditions(1).Interior.Color = 5296274 ' Green for non-zero values in column B
[/B]'The rest works fine
.Range("B1:D1").HorizontalAlignment = -4152 ' Right Justify B1-D1
.Cells.Select
.Cells.EntireColumn.AutoFit ' Autofit entire sheet
.Range("A1").Select ' Select top left cell
' Save workbook
.ActiveWorkbook.Save
.Workbooks.Close
End With
Excelsheet.Application.Quit
The same two lines work fine if pasted into a module in the actual spreadsheet, but that is not what I want to do, for various reasons.
Thanks