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

Leave a Reply

Your email address will not be published. Required fields are marked *