VBA Cell Font – Change Color, Size, Style, & More

VBA Cell Font

In VBA, you can change font properties using the VBA Font Property of the Range Object. Type the following code into the VBA Editor and you’ll see a list of all the options available:

Range("A1).Font.

We will discuss a few of the most common properties below.

Change Font Color

There are a few ways to set font colors.

vbColor

The easiest way to set colors is with vbColors:

Range("a1").Font.Color = vbRed

However, you’re very limited in terms of colors available. These are the only options available:

Color – RGB

You can also set colors based on RGB (Red Green Blue). Here you enter color values between 0-255 for Red, Green, and Blue. Using those three colors you can make any color:

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

ColorIndex

VBA / Excel also has a ColorIndex property. This makes pre-built colors available to you. However, they’re stored as Index numbers, which makes it hard to know what color is what:

Range("a1").Font.ColorIndex = …..

We wrote an article about VBA Color codes, including a list of the VBA ColorIndex codes. There you can learn more about colors.

Font Size

This will set the font size to 12:

Range("a1").Font.Size = 12

or to 16:

Range("a1").Font.Size = 16

Bold Font

It is easy to set a cell font to Bold:

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

or to clear Bold formatting:

Range("A1").Font.Bold = False

Font Name

To change a font name use the Name property:

Range("A1").Font.Name = "Calibri"

Range("A1").Font.Name = "Arial"

Range("A1").Font.Name = "Times New Roman"

Cell Style

Excel offers the ability to create Cell “Styles”. Styles can be found in the Home Ribbon > Styles:

Styles allow you to save your desired Cell Formatting. Then assign that style to a new cell and all of the cell formatting is instantly applied.  Including Font size, cell color, cell protections status, and anything else available from the Cell Formatting Menu:

Personally, for many of the models that I work on, I usually create an “Input” cell style:

Range("a1").Style = "Input"

By using styles you can also easily identify cell types on your worksheet.  The example below will loop through all the cells in the worksheet and change any cell with Style = “Input” to “InputLocked”:

Dim Cell as Range

For Each Cell in ActiveSheet.Cells
  If Cell.Style = "Input" then
    Cell.Style = "InputLocked"
  End If
Next Cell