How to Format Cells in Excel Using VBA (With Examples)

There are a wide variety of ways to format cells in Excel by using various properties in VBA, including:

• AddIndent
• Application
• Borders
• Creator
• Font
• FormulaHidden
• HorizontalAlignment
• IndentLevel
• Interior
• Locked
• MergeCells
• NumberFormat
• NumberFormatLocal
• Orientation
• Parent
• ShrinkToFit
• VerticalAlignment
• WrapText

By creating a macro in VBA, you can use one or more of these properties to format cells in a specific range in an Excel sheet.

The following example shows how to do so in practice.

Example: How to Format Cells in Excel Using VBA

Suppose we have the following list of basketball team names in Excel:

We can create the following macro to format each of the cells in the range A2:A11 with specific properties:

Sub FormatCells()

    With Worksheets("Sheet1").Range("A2:A11")
     .Font.FontStyle = "Bold"
     .Font.Name = "Calibri"
     .Font.Size = 13
     .Font.Color = vbRed
     .HorizontalAlignment = xlCenter
    End With

End Sub

Once we run this macro, each of the cells in the range A2:A11 will be formatted in the ways that we specified:

Using this particular macro, we made the following changes to each of the cells in the range A2:A11:

• We changed the font style to bold.
• We changed the font family to Calibri.
• We changed the font size to 13.
• We change the font color to red.
• We centered the text horizontally.

Note that this example shows just one way to format cells in a particular range.

Using the various properties available in VBA, you can format the cells to look however you would like.

Note: You can find the complete documentation for all possible cell formatting properties in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Make Font Bold Using VBA
How to Center Text Using VBA
How to Set Font Size Using VBA