Utilizing Conditional Formatting in Excel from Access (1 Viewer)

[PatrickJohnson]

I'm trying to apply some conditional formatting to some rows in an excel spreadsheet via access vba code.

basically, i have a list of tables, lets say A thru D. I have a routine that exports these tables to separate sheets in an common excel file. I am doing this by looping through a recordset with the table names in it, and exporting each table without changing the name of the destination file. This works fine.

then, I'm calling a separate function which, given the name of the table and the path of the excel workbook, opens up the workbook, activates the appropraite sheet (named for the table it came from), and performs some formatting on the sheet. then it closes the sheet and workbook, and the original routine moves to the next table name in the recordset to repeat the process. this also works fine, except...

there are some lines in the code that attempt to apply conditional formatting the the excel sheet. this works fine on the first time through, but when the original routine then moves on to the next record (table name), i get the "object variable or with block not set blah blah" error we all know and love. so what gives? the loop routine is identical for each table, the only difference i can see is that the file has already been created and has a sheet in it already once i attempt to format the next one. here is the formatting subroutine..

Function FormatMonthlyHours(ByVal FilePath As String, ByVal strSheetName As String)
Dim xlapp As Object
Dim xlws As Worksheet
Dim xlwb As Workbook
Dim intRow, intTot As Integer
Dim dblhours As Double
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
Set xlwb = Workbooks.Add(FilePath)
Set xlws = xlwb.Worksheets(strSheetName)
With xlws
    .Activate
    .Cells.Select
    .Cells.EntireColumn.AutoFit
    .Range("A1").End(xlToRight).Select
    .Range("A1").End(xlDown).Select
    .Range("A1", "B1").Interior.ColorIndex = 1
    .Range("A1", "B1").Font.ColorIndex = 2
    .Range("A1", "B1").Font.Bold = True
    .Columns("B:B").End(xlDown).Select
    .Cells.NumberFormat = "0.00"
    End With

    Range("A2", Selection.End(xlToRight)).Select
    Range("A2", Selection.End(xlDown)).Select

'THIS IS WHERE I GET THE ERROR
--------------------------------------------------------------
    xlapp.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"
    xlapp.Selection.FormatConditions(1).Interior.ColorIndex = 15

    Range("B2", Selection.End(xlToRight)).Select
    Range("B2", Selection.End(xlDown)).Select
    xlapp.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"
    
     xlapp.Selection.FormatConditions(1).Interior.ColorIndex = 15
-----------------------------------------------------------------

intRow = 2
xlapp.DisplayAlerts = False
xlwb.Close True, FilePath
xlapp.Quit
xlapp.DisplayAlerts = True
Set xlapp = Nothing
Set xlwb = Nothing
Set xlws = Nothing
End Function

If I remove the conditional formatting, it all runs fine. Any suggestions?