Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac
Key Notes
- To make changes in a font, you need to use the VBA Font object.
- There is a total of 18 properties with the font object that you can access and make changes.
VBA Font Object
In VBA, there is a font object which you can use to change properties of the font from a cell, like, font color, font size, font type, and you can also apply bold and italic to the font.
Syntax
expression.font
Things you can do with the help of VBA Font Object
- Font Style: You can change the font style to a different typeface. For example, you can change the text in a cell to Arial, Times New Roman, or any other font installed on your computer.
- Font Size: You can adjust the size of the text, making it larger or smaller to suit your needs.
- Font Color: The text color can be changed to any color in Excel’s color palette. This can be useful for highlighting important data or matching a specific color scheme.
- Bold, Italic, and Underline: The Bold, Italic, and Underline properties of the Font object allow you to apply or remove these basic text effects.
- Special Effects: Beyond the basic text effects, the Font object also allows for special effects such as strikethrough, superscript, and subscript.
To use it, first, you need to define the cell address, which you can specify in the following ways.
'refers to the Font object of the currently selected cells
Selection.Font
'refers to the Font object of the cell A1
Range("A1").Font
'refers to the Font object of the cell in the first row and first column (cell A1)
Cells(1, 1).Font
'refers to the Font object of the range from A1 to A5
Range("A1:A5").Font
VBA Font Color
To change the color of the font, you have two different ways:
1. Using Color Constants
Excel has a few color constants that you can use to apply color to the font.
For example, if you want to apply the red color to the font in cell A1, the code would be like the below:
Range("A1").Font.Color = vbRed

In the above code, after the font object, color is the property and you have used the vbRed constant that tells VBA to apply the red color to cell A1.
There is a total of eight constants that you can use:
- vbBlack: Black
- vbRed: Red
- vbGreen: Green
- vbYellow: Yellow
- vbBlue: Blue
- vbMagenta: Magenta
- vbCyan: Cyan
- vbWhite: White
2. Using RGB
You can also use the RGB color code to apply color to the font.
RGB is the combination of red, green, and blue colors, where you can create a custom color using the code.
Let’s say if you want to apply a combination of green and blue color to cell A1 the code would be:
Range("A1").Font.Color = RGB(0, 255, 255)

VBA Font ColorIndex Property
In VBA, you can change the font color using the ColorIndex property. This property uses a numeric value to represent a color.
For example, the numeric value 1 represents black, 2 represents white, and 3 represents red. The ColorIndex property in total offers 56 different color options.
Here’s an example:
Range("A1").Font.ColorIndex = 3
In this example, the font color of cell “A1” is set to red. As I said, the ColorIndex property uses a number to represent a color.
VBA Font Size
Font object also gives you access to the size property of the font.
Let’s say you want to apply the font size of 16 to the font in the cell A1, the code would be:
Range("A1").Font.Size = 16

If you want to apply font size to all cells in a worksheet you can use the following code:
Cells.Font.Size = 16
And if only want to apply font size to cells where you have data, the code would be:
ActiveSheet.UsedRange.Font.Size = 16
Or to the selected cell.
Selection.Font.Size = 16
VBA Font Name
In the same way, you can also change the font name using the name property of the font object.
Let’s say you want to apply the “Consolas” font the cell A1. The code would be:
Range("A1").Font.Name = "Consolas"

While using this property, you need to type the correct name of the font that you want to apply, and if somehow the name is incorrect, it won’t show you an error.
VBA Font Bold, Italic, and Underline
There are also properties that you can use to make the font bold, italic, and underline.
Below are the codes that you need to write for this.
'to apply
Range("A1").Font.Bold = True
Range("A1").Font.Italic = True
Range("A1").Font.Underline = True
'to remove
Range("A1").Font.Bold = False
Range("A1").Font.Italic = False
Range("A1").Font.Underline = False

With these properties, you need to define TRUE or FALSE.
So if the font is already bold or italic and you want to remove it, then you need to use FALSE to remove them.
Other Useful Font Properties
Here add a few more properties that can be useful for you (Strikethrough, Subscript, and Superscript).
'to apply
Range("A1").Font.Strikethrough = True
Range("A1").Font.Subscript = True
Range("A1").Font.Superscript = True
'to remove
Range("A1").Font.Strikethrough = True
Range("A1").Font.Subscript = True
Range("A1").Font.Superscript = True
Related Tutorials
- Count Rows using VBA in Excel
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells