Q:

How to format an Excel table using formula?

I'm creating an Excel spreadsheet dynamically from information in my database using ColdFusion. 
I discovered that I can insert Excel formulas into cells and they will automatically be executed. I was wondering if it's possible to utilise Excel's 'Format as table' feature through a formula or macro, or is there another way to do this?

A:

You can do this through a macro like follows
Private Sub Workbook_Open()

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$10"), , xlNo).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"

End Sub

This will format cells A1 through D10 As a Table using the TableStyleLight9 style which is blue by default.

