VBA Cell Font – Change Color, Size, Style, & More
In this Article
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:
We will discuss a few of the most common properties below.
Change Font Color
There are a few ways to set font colors.
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)
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.
This will set the font size to 12:
Range("a1").Font.Size = 12
or to 16:
Range("a1").Font.Size = 16
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
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"
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