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

Associated Files Download Links

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:

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:

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:

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:

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:

or to 16:

 

Bold Font

It is easy to set a cell font to Bold:

or to clear Bold formatting:

 

Font Name

To change a font name use the Name property:

 

 

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:

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”:

 

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon