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

VBA Cell Font

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

Range("A1).Font.

vba cell 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:

vba vbcolor

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:

excel vba font 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:

cell formatting menu excel

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

 

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)