VBA Text Formatting And Font Formatting

Formatting text is one of the most powerful ways you can improve the adoption and usability of your Excel files. Excel has robust text formatting and font capability and these are available in VBA.

Writing a macro that can insert text with certain text attributes and fonts or manipulate existing cells formatting can come handy for your more complex spreadsheets. This post will explore how you set the most common text formatting options and how you set fonts.

VBA Text Formatting Attributes

First, let’s create a simple macro that inserts some text into the first cell of a worksheet:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
 End Sub

To make your font bold, modify your macro to:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
     Worksheets("Sheet1").Cells(1, "A").Font.Bold = True
 End Sub

To make your text italic, modify your macro to:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
     Worksheets("Sheet1").Cells(1, "A").Font.Italic = True
 End Sub

To change the color of the text, you need to use the RGB function. It takes in 3 numbers and returns the associated color:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
     Worksheets("Sheet1").Cells(1, "A").Font.Color = RGB(255, 0, 0)
 End Sub

To change text size:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
     Worksheets("Sheet1").Cells(1, "A").Font.Size = 36
 End Sub

VBA Font Formatting

To set the font of the text, use the following code and specify the name of the font you want:

Sub FormattingMacro()
     Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
     Worksheets("Sheet1").Cells(1, "A").Font.Name = "Times New Roman"
 End Sub

Try combining these techniques with other VBA tricks to make something unique!