Lesson#124: How to format text in excel with VBA

Another step towards learning VBA. In this post we will learn to format text in excel with VBA.

In the previous post I discussed about changing cell background color and about color codes.

So we will start in a little advance mode here. I am here taken an example of cell A1. I am going to show how to change font, text color, text size, how to change bold, italic, underline etc.

Ok then open the VBA editor by pressing Alt+F11 and then select and double click the sheet here.

Change background and text color

Now put code like this to change background and text color.

Sub color()
Range(“A1”).Interior.color = vbGreen
Range(“A1”).Font.color = vbRed
End Sub

This will change the background color to vbGreen or RGB(0, 255, 0) and text color into vbRed or RGB(255, 0, 0).

Change text size

Now I will add another line to change text size.

Sub color()
Range(“A1”).Interior.color = vbGreen
Range(“A1”).Font.color = vbRed
Range(“A1”).Font.Size = 24
End Sub

The size of text is changed in VBA like this way. I have changed into 24.

Change bold, italic, underline etc.

Now I will add more lines to show you how to change text to bold or italic and others.

Sub color()
Range(“A1”).Interior.color = vbGreen
Range(“A1”).Font.color = vbRed
Range(“A1”).Font.Size = 24
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Bold = True
Range(“A1”).Font.Underline = True
Range(“A1”).Font.Strikethrough = True
End Sub

This has made the text bold, italic, underlined, strikethrough.

Change font styles.

Now I will add one more line to show how to change font style.

Sub color()
Range(“A1”).Interior.color = vbGreen
Range(“A1”).Font.color = vbRed
Range(“A1”).Font.Size = 24
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Bold = True
Range(“A1”).Font.Underline = True
Range(“A1”).Font.Strikethrough = True
Range(“A1”).Font.Name = “Arial Black”
End Sub

I have changed to font style into Arial Black

Change cell border

This will show you how to change cell border.

Sub color()
Range(“A1”).Interior.color = vbGreen
Range(“A1”).Font.color = vbRed
Range(“A1”).Font.Size = 24
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Bold = True
Range(“A1”).Font.Underline = True
Range(“A1”).Font.Strikethrough = True
Range(“A1”).Font.Name = “Arial Black”
Range(“A1”).Borders.Weight = xlThick
End Sub

I have added a thick border so I used xlThick

for thin border you have to replace with xlThin

It finally looks like this.

So we have learned about text formatting in VBA. I hope you liked this post. 

Puspendu is the founder author of Excelabcd. He is a creative person, blogger and Excel-maniac guy.

Leave a Reply

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

*