There are a wide variety of ways to format cells in Excel by using various properties in VBA, including:
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.
The following tutorials explain how to perform other common tasks in VBA: