How to Format Text Using Excel VBA (12 Methods)

In this article, we’ll illustrate how to change the format of a text in Excel using VBA code. While working with a large amount of information, formatting text with different styles is useful to make a clear illustration that helps readers to understand easily. Using VBA code is a quick way to select a range of cells and apply the desired format like color, size, effects, alignment, or orientation.

Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.

12 Suitable Methods of Changing Format of a Text Using VBA in Excel (with Examples)

In our dataset, we used a list of cities in the USA and some random text to make a clear illustration of how to format a text in different ways.

1. Change Text Alignment of a Text

1.1 Horizontal Alignment

Excel provides different ways to align a text horizontally.

• General: Excel’s default type of text alignment.
• Center: It aligns the text in the middle of the cell.
• Distributed: This type of alignment distributes all the contents equally across the width of the cell.
• Justify: Justify type of alignment forces the contents of a cell to fit within the cell width and wrapping text to additional lines if necessary.
• Left: Left alignment means text will be aligned to the left side of the cell.
• Right: Text will be aligned right to the cell width.

Code:

In column B, we put the names of the largest cities in the USA in the cells C5:C10. These texts are not formatted horizontally; just have the default alignment format. In column C, we used the following VBA code to align them based on different properties. We extended the normal cell width to make a clear illustration of the changes made. Here is the code:

Sub horizontalAlignment()
Range("C5").horizontalAlignment = xlGeneral
Range("C6").horizontalAlignment = xlCenter
Range("C7").horizontalAlignment = xlDistributed
Range("C8").horizontalAlignment = xlJustify
Range("C9").horizontalAlignment = xlLeft
Range("C10").horizontalAlignment = xlRight
End Sub

1.2 Vertical Alignment

There are several ways to align a text vertically.

• Bottom: Bottom alignment makes text appear at the bottom of the cell.
• Center: In this case, text aligns halfway between the top and bottom border of the cell.
• Justify: Text spread evenly throughout the cell.
• Distributed: Text gets distributed evenly between the top of the cell and the bottom.
• Top: It aligns text to settle the first line of the text at the top of the cell.

In column B, we put the names of the largest cities in the USA in the cells B5:B9. These texts are not formatted vertically; just have the default alignment format. In column C, we used the following VBA code to align them base on different properties.

Just copy the texts from column B to column C in the cells C5:C9 and then apply the following code. We extended the normal cell height to make a clear illustration of the changes made. Here is the code.

Code:

Sub verticalAlignment()
Range("C5").verticalAlignment = xlBottom
Range("C6").verticalAlignment = xlCenter
Range("C7").verticalAlignment = xlDistributed
Range("C8").verticalAlignment = xlJustify
Range("C9").verticalAlignment = xlTop
End Sub

2. Apply Text Control Format to a Text Using Excel VBA 

2.1 Wrap Text

This format wraps extra-long text into multiple lines so that the reader can see all of it.

In cell C4, there is a long text which is wrapped with the following code. The same text is also there in cell B4 without wrapping.

Write the same text that of cell B4 in cell C5 and then apply the code to see the difference.

Code:

Sub textControl()
Range("C5").WrapText = True
End Sub

2.2 Shrink to Fit

This formatting shrinks a text in a row to fit in the column width automatically.

The text in B6 is in default format which is then copied to C6 and shrunk by following the code below.

Code:

Sub textControl()
Range("C6").ShrinkToFit = True
End Sub

3. Use of VBA to Alter Reading Order of a Text in Excel

Text direction property has three constants to set the reading order of a text. It may or may not have an effect based on the language support that is installed. For example, the English language has no effect. Types of text direction properties are:

• xlRTL– Right to Left.
• xlLTR– Left to Right.
• xlContext

Code:

Sub textDirection()
Range("A1").ReadingOrder = xlRTL
Range("A1").ReadingOrder = xlLTR
Range("A1").ReadingOrder = xlContext
End Sub

Similar Readings

• Dealing with Time Format in Excel (5 Suitable Ways)
• How to Custom Format Cells in Excel (17 Examples)
• Use Format Painter in Excel (7 Ways)
• How to Copy Cell Format in Excel (4 Methods)

4. Set Text Orientation with VBA Code in Excel

Using VBA code in Excel we can change text orientation very quickly. There are several options you can choose to change text orientation. They are:

• Horizontal
• Vertical
• Downward
• Upward

There are 4 texts in cells B5:B8 which are in Excel’s default orientation. In column C, right to column B, we copied the texts into cells C4:C8 first and then applied the following VBA code to alter their orientation.

Code:

Sub orientation()
Range("C5").orientation = xlHorizontal
Range("C6").orientation = xlVertical
Range("C7").orientation = xlDownward
Range("C8").orientation = xlUpward
End Sub

5. Apply Different Font Format to a Text Using Excel VBA Code

5.1 Font Name

Using VBA code, we can set a font like Calibri, New Times Roman, Arial, Arial Black, Atma, etc. In the example, we set these font names to cells C5:C9.

Code:

Sub fontName()
'code for changing font name
Range("C5").font.Name = "Arial"
Range("C6").font.Name = "Calibri"
Range("C7").font.Name = "New Times Roman"
Range("C8").font.Name = "Arial Black"
Range("C9").font.Name = "Atma"
End Sub

5.2 Font Style

Different font styles like Bold, Italic, Regular, and Bold Italic can easily be applied to a text following simple VBA code. This example shows how to style a text in cells C5:C8.

Code:

Sub fontStyle(
'code for changing font style
Range("C5").font.FontStyle = "Italic"
Range("C6").font.FontStyle = "Bold"
Range("C7").font.FontStyle = "Regular"
Range("C8").font.FontStyle = "Bold Italic"
End Sub

5.3 Font Size

We can control the font size of a text by applying Excel VBA code. In the example, we set different font sizes in cells C5:C9.

Code:

Sub fontSize()
'code for changing font size
Range("C5").font.Size = 10
Range("C6").font.Size = 12
Range("C7").font.Size = 14
Range("C8").font.Size = 16
Range("C9").font.Size = 18
End Sub

5.4 Underline

We can also apply different type of underline formatting to a text. There are several options like  xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting.  In this illustration, we applied different types of text underline formats in cells C5:C9.

Code:

Sub underline()
'code for underline text
Range("C5").font.underline = xlUnderlineStyleNone
Range("C6").font.underline = xlUnderlineStyleSingle
Range("C7").font.underline = xlUnderlineStyleDouble
Range("C8").font.underline = xlUnderlineStyleSingleAccounting
Range("C9").font.underline = xlUnderlineStyleDoubleAccounting
End Sub

5.5 Font Color

Different font colors vbRed, vbGreen, vbBlack can be applied to a text using VBA code. We can also apply the RGB function which gives us the option to vary the color palette.

Syntax of RGB function is:

RGB(Red As Integer, Green As Integer, Blue As Integer) As Long

This function takes three arguments as integers whose value ranges from 1 to 255. That means we are given 255*255*255 = 16, 581, 375 color combinations to put to format a text.

In this example, we put different colors in cell C5:C9 in the code.

Code:

Sub color()
'code for changing font color
Range("C5").font.color = vbRed
Range("C6").font.color = vbGreen
Range("C7").font.color = vbYellow
Range("C8").font.color = RGB(0, 255, 255)
Range("C9").font.color = RGB(255, 255, 255)
End Sub

6. Change Font Effects Format of a Text by Applying Excel VBA Code

6.1 Strikethrough

It would draw a line through data in a cell which can be useful to show edits or completed tasks.

Code:

In this illustration, cell B5 contains text without any formatting. But in the next column C, in cells C5, we applied the Strikethrough effect. The code here below:

Sub Strikethrough()
'code--Strikethrough
Range("C5").font.Strikethrough = True
End Sub

6.2 Subscript

A subscript is a number, symbol, indicator, or figure which is slightly smaller than the normal line of type and is set slightly below it.

Code:

Cell B6 contains text without any formatting. But in the next column C, in cells C6, we applied the Subscript effect. The code here below:

Sub Subscript()
'code--Subscript
Range("C6").font.Subscript = True
End Sub

6.3 Superscript

A superscript is a number, symbol, indicator, or figure which is slightly smaller than the normal line of type and is set slightly above it.

Code:

In this example, cell B7 contains text without any formatting. But in the next column C, in cell C7, we applied the Superscript effects. The code here below:

Sub Superscript()
'code--Superscript
Range("C7").font.Superscript = True
End Sub

Things to Remember

• We need to be careful while selecting the range of cells to format in a large dataset. Otherwise, once we run the code it will change the format of data unexpectedly.
• While writing multiple codes it is better to keep the right sequence.

Conclusion

Now, we know how to change the format of a text using VBA in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Further Readings

• Use Excel Cell Format Formula (4 Effective Methods)
• How to Use Format Painter Shortcut in Excel (5 Ways)
• Change Time Format in Excel (4 Ways)
• How to Copy Formatting in Excel to Another Sheet (4 Ways)