In this Article
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:
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