Hello,
I am currently exporting multiple queries in excel using transferspreasheet command. This is all working fine however what i want to do now is when i am exporting to excel and formatting excel using excel automation, i want to highlight the blank cells in usedrange to highlight in yellow. Does anyone have any ideas? here some code to help
I get an error in the red part and its Run time error '424' Object Required.
please help.
thank you,
Summer
I am currently exporting multiple queries in excel using transferspreasheet command. This is all working fine however what i want to do now is when i am exporting to excel and formatting excel using excel automation, i want to highlight the blank cells in usedrange to highlight in yellow. Does anyone have any ideas? here some code to help
Code:
Case acCheckBox
If Not ctl.Name Like "*_Errors" And Not ctl.Name Like "*_rpts" Then
If ctl = True Then
Set rst = db.OpenRecordset(ctl.Name)
If Not rst.EOF Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
End If
rst.Close
Set rst = Nothing
End If
End If
End Select
Next ctl
strFileName = Dir("C:\temp\Errors.xls")
If strFileName = "Errors.xls" Then
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(fileIn)
intCountofSheets = xlBook.sheets.Count
intCurrentSheet = 1
Do While intCurrentSheet <= intCountofSheets
xlBook.Worksheets(intCurrentSheet).Activate
With xlApp.ActiveSheet.UsedRange
.Font.Name = "Tahoma"
.Font.Size = 10
.rows(1).Font.Bold = False
.rows(1).interior.colorindex = 36
.cells.Select
.cells.EntireColumn.AutoFit
.cells.EntireRow.AutoFit
[COLOR=red] 'If cells.Value Is Null Then - so this is what i want to do... but i am getting an error[/COLOR]
[COLOR=red] ' cells.interior.colorindex = 36[/COLOR]
[COLOR=red] ' End If[/COLOR]
I get an error in the red part and its Run time error '424' Object Required.
please help.
thank you,
Summer
Last edited: