Format Cell & Text Font in Excel VBA (Color, Size, Format, Name, Style)

In this article, you will learn how to format cells and text font using Excel VBA.

Example 1: Format Text Font Color by Excel VBA

You can use the three best-using properties to customize text color, these are vbColor, ColorIndex, and RGB values. 

1.1 Use vbColors

As an illustration, in cell B2, you want the text to be blue. Use the following code to do this:

Range("B2").Font.Color = vbBlue

There are almost 16 vbColors available to use through the VBA code.

• vbBlack
• vbRed
• vbGreen
• vbYellow
• vbBlue
• vbMagenta
• vbCyan
• vbWhite
• vbGray
• vbGrey
• vbMaroon
• vbOlive
• vbDarkGreen
• vbDarkRed
• vbDarkBlue
• vbDarkYellow

In the same way, assign different colors to different cells within range B2:B8 by using the following Codes.

Sub vbColorName()
Range("B2").Font.Color = vbBlue
Range("B3").Font.Color = vbBlack
Range("B4").Font.Color = vbGreen
Range("B5").Font.Color = vbCyan
Range("B6").Font.Color = vbMagenta
Range("B7").Font.Color = vbRed
Range("B8").Font.Color = vbYellow
End Sub

After running the code, see the output image for the range B2:B8. Column C is a helper column to understand the color.

1.2 Use ColorIndex

In addition to the vbColor property, you can use the VBA ColorIndex property to do the same.

There are 56 ColorIndex properties available for 56 different colors. 

For instance, you want to change cell D3’s default text color to red. So, you can apply the following codes by using the ColorIndex property 3.

Range("D3").Font.ColorIndex = 3 

Use the ColorIndex property to color each cell in the range D2:D9 by applying the following codes. 

Sub vbColorIndexFont()
Range("D2").Font.ColorIndex = 1
Range("D3").Font.ColorIndex = 3
Range("D4").Font.ColorIndex = 5
Range("D5").Font.ColorIndex = 7
Range("D6").Font.ColorIndex = 9
Range("D7").Font.ColorIndex = 11
Range("D8").Font.ColorIndex = 13
Range("D9").Font.ColorIndex = 15
End Sub

Thus, see the changes in column D by applying the above VBA codes with different ColorIndex.

1.3 Use RGB Values

The most suitable approach is probably to color your text font using RGB values. because by modifying the RGB values, you can create any color you like. There are many different color variations available thanks to the RGB value range of 0 to 255.

For instance, you might want to restore the previous red color of the text font. Simply enter the following code for cell text D2 to modify now.

Range("D2").Font.Color = RGB(255, 0, 0)

Apply the following codes to different cells in the range D2:D9 to create different font colors.

Sub Font_RGB()
Range("D2").Font.Color = RGB(255, 0, 0)
Range("D3").Font.Color = RGB(0, 255, 0)
Range("D4").Font.Color = RGB(0, 0, 255)
Range("D5").Font.Color = RGB(255, 255, 0)
Range("D6").Font.Color = RGB(0, 255, 255)
Range("D7").Font.Color = RGB(255, 0, 255)
Range("D8").Font.Color = RGB(52, 7, 45)
Range("D9").Font.Color = RGB(0, 0, 0)
End Sub

See the output in column D for applying the above VBA codes with RGB values.

Example 2: Format Text Font Style

In addition to using Excel Basic’s feature, you can also change your text style using VBA code. A text’s default font style can be changed to bold, italic, underline, strikethrough, subscript, superscript, and other styles. 

2.1 Bold Text

To bold a text, apply the following code with the Boolean True expression.  

Range("D2").Font.Bold = True

2.2 Italic Text

To italicize a text, apply the following code with the Boolean True expression.

Range("D3").Font.Italic = True

2.3 Underline Text

To underline a text, apply the following code with the Boolean True expression.

Range("D4").Font.Underline = True

2.4 Strikethrough Text

To put a line through a text, apply the following code with the Boolean True expression.

Range("D5").Font.Strikethrough = True

2.5 Subscript Text

To lower a text below the baseline, apply the following code with the Boolean True expression.

Range("D6").Font.Subscript = True

2.6 Superscript Text

To raise a text above the baseline, apply the following code with the Boolean True expression.

Range("D7").Font.Superscript = True

2.7 Change Font Size

To change the font size to 15, apply the following code.

Range("D8").Font.Size = 15

2.8 Change Font Name

To change the font name to “Bookman Old Style”, apply the following code. 

Range("D9").Font.Name = "Bookman Old Style"

See all the codes in a nutshell. 

Sub Text_Style()
Range("D2").Font.Bold = True
Range("D3").Font.Italic = True
Range("D4").Font.Underline = True
Range("D5").Font.Strikethrough = True
Range("D6").Font.Subscript = True
Range("D7").Font.Superscript = True
Range("D8").Font.Size = 15
Range("D9").Font.Name = "Bookman Old Style"
End Sub

See the output in column D with all the applied text font style formatting.

Example 3: Format Cell Theme Style

You can also format a cell theme across a range (C2:C9) at once by applying the For loop and If statement in the following code below. In this code, we want to change the cell theme style from “Normal” to “Bad.” 

Sub Cell_Style()
Dim xCell As Range
For Each xCell In Range("C2:C9")
If xCell.Style = "Normal" Then
    xCell.Style = "Bad"
  End If
Next xCell
End Sub

So, see the output for applying the codes to make change the cell theme style.

Conclusion

In the above discussion, we have shown you how to change text and cell fonts with different styles using Excel VBA. We hope this information will be useful in your practical applications. If you have any additional questions, kindly post them in the comment section below. You will answer by offering solutions from the Solved Excel research department. Stay safe and get connected with us!