Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Create and Format a Table

Advance VBA applications consist of various functions. Report generating is one of them.  Because lots of applications need to generate various kinds of reports for the user.  These reports contains data in various formats such as tables, charts etc. So when we create reports we often need to create tables dynamically. Because in developing Stages we don't know where the table will start and end because they often depend on the data entered by the user. So in this post I will explain you how to develop a subroutine to create and format a table.  So then you can call from anywhere in your main program. We are creating this table on a excel sheet.  I will explain the steps one by one.

This subroutine need 5 parameters.  We need to input sheet name, start column, end column, starting row and end row.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long)

Assume that you call subroutine as follows in main program.

Dim WS_2 As Worksheet

Set WS_2 = Worksheets("Sheet2")

Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

So it will create a table in range B3:F17.
We can use With WS to make it easier to write and read the code.

With WS

End with

First we need to select the range.

.Range(StartCol & StartRow & ":" & EndCol & EndRow).Select



Next we need to add borders.

'---------------------------------------
'Add borders
'---------------------------------------
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With


 

Then colour the header row

'---------------------------------------
'Background Color of headers
'---------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 12611584
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
 


You can change the colours in above code to suit with your requirements. Now we can color rest of the area. I will color every other row in light blue color. But you can change it to color you prefer.

'-----------------------------------------
'Background color of other area
'-----------------------------------------
For i = StartRow + 2 To EndRow Step 2
    .Range(StartCol & i & ":" & EndCol & i).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
Next i


Next we need to set the font name and font size. I will choose “Arial” and size 11.


'-----------------------------------------
'All table font name and size
'-----------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
With Selection.Font
    .Name = "Arial"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

So if you have data in the range, it will look like this now.

Now we can set the header font color and also I will make the font bold. I will choose the white color for header fonts.

'---------------------------------------------
'Set header font color and make the font bold
'---------------------------------------------
.Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
Selection.Font.Bold = True

You will get following result if you have data in the range.




So now our subroutine is complete. We can call the subroutine whenever we need.  To make your life easier I will put the whole code of the subroutine below.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long)

With WS

    Dim i As Long

    .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
   
    '---------------------------------------
    'Add borders
    '---------------------------------------
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    '---------------------------------------
    'Background Color of headers
    '---------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    '-----------------------------------------
    'Background color of other area
    '-----------------------------------------
    For i = StartRow + 2 To EndRow Step 2
        .Range(StartCol & i & ":" & EndCol & i).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    Next i
    '-----------------------------------------
    'All table font name and size
    '-----------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    '---------------------------------------------
    'Set header font color and make the font bold
    '---------------------------------------------
    .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
   
    .Range("A2").Select

End With

End Sub

You can call the subroutine as follows in main program.

Dim WS_2 As Worksheet

Set WS_2 = Worksheets("Sheet2")

Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

Contact Form

Name

Email *

Message *