Highlight Top Row in Excel for Only Cells with Values | MS Access

Question

Essentially I have the following code I use in MS Access after Exporting an Excel. This works well for Applying the Filter Functionality and Making the Cell Values Bold, but how can I also highlight the cells of those same values?

   'Specifying Tables

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table1",".xlsx", True


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table2",".xlsx", True

Call ModifyExportedExcelFileFormats(".xlsx", "Table1")

Call ModifyExportedExcelFileFormats(".xlsx", "Table2")

__

Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)



With xlApp
        .Application.Sheets(sSheet).Select
        .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
        
        .Application.range("A1").Select
        .Application.Selection.AutoFilter
        
        
        .Application.Cells.Select
        .Application.Selection.Columns.AutoFit
        .Application.range("A1").Select
        
        .Application.Activeworkbook.Save
        .Application.Activeworkbook.Close
        .Quit
   End With

End Sub

How can this properly used to only highlight the cells in the top row that have values in them?

        .Application.Selection.Interior.Color = vbYellow

Range([whatever]).SpecialCells(xlCellTypeConstants).Interior.Color = vbYellow? — BruceWayne
I've updated my post with some more context, can a conditional rule be applied to the spreadsheet through MS Access VBA? All formatting I apply to it now is done in silently after I export — Charlin

June7 June7 · Accepted Answer · 2020-08-07T04:05:12

This does work:
Range([whatever]).SpecialCells(xlCellTypeConstants).Interior.Color = vbYellow

But if users will be editing data, might want to set Conditional Formatting rule. It is not necessary to actually select cells to modify properties. Using ActiveAnything syntax can cause issues with automation code. Better to use explicit reference.

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(sFile)
With xlWb.Worksheets(sSheet).Range("A1:G1")
    .Font.Bold = True
    .Columns.AutoFit
    .FormatConditions.add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))>0"
    .FormatConditions(1).Interior.Color = vbYellow
End With
xlWb.Worksheets(sSheet).Range("A1").AutoFilter
xlWb.Save
xlWb.Close
xlApp.Quit

Highlight Top Row in Excel for Only Cells with Values | MS Access

1 Answers