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.
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.
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.
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.
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.
Sub textDirection() Range("A1").ReadingOrder = xlRTL Range("A1").ReadingOrder = xlLTR Range("A1").ReadingOrder = xlContext End Sub
- 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:
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.
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.
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.
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.
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
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.
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.
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
It would draw a line through data in a cell which can be useful to show edits or completed tasks.
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
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.
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
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.
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.
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.