Excel VBA Font: 2 Macro Examples To Easily Specify Font Characteristics

From time-to-time, you may need to use Visual Basic for Applications for purposes of working with fonts. For example, you may want to:

Apply bold or italic style.
Specify the font color or size.
Set the characteristics of the text underline.

If you’re in a situation where you want to modify a font attribute, this VBA Tutorial should help you.

My purpose with this blog post is to provide you with the knowledge you need to specify virtually any font characteristic using VBA. You may be surprised by the amount of options you have and the large quantity of font attributes you can specify.

In fact, the large number of VBA constructs that have an influence on how a font looks can be overwhelming. However, you shouldn’t worry too much…

Within this Tutorial, you’ll find the following:

The easy 2-step process that you can use to structure a macro or a VBA statement that works with font attributes.
The 2 VBA objects that, usually, contain the font characteristics of the object you’re working with.
A detailed description of the most relevant properties and methods you can use to specify font attributes.
A step-by-step explanation of 2 detailed macro examples that work with fonts.

In this section, I show you dozens of different lines of code that you can easily adapt for your own purposes.

This blog post is accompanied by an Excel workbook. This workbook contains (i) the 2 sample macros I explain in the last section of this Tutorial, and (ii) the worksheet that I use to test the macros and create the screenshots shown throughout the post. You can get immediate free access to this workbook by clicking here.

The following table of contents lists the main topics I cover in this blog post:

Table of Contents

How Do You Specify Font Attributes With VBA
VBA Font And Font2 Objects
- Properties Of The Font Object
- Properties Of The Font2 Object
Macro Code Examples To Work With Excel VBA Fonts
Conclusion
Books Referenced In This Excel VBA Tutorial

Let’s start by looking at a basic question:

How Do You Specify Font Attributes With VBA

The topic of specifying font attributes with Visual Basic for Applications can be surprisingly long. At first glance, it may seem complicated.

However, don’t be overwhelmed by the amount of VBA constructs you can work with.

As Excel guru John Walkenbach explains in Excel VBA Programming for Dummies:

To change some aspect of a range’s font, you must first access the range’s Font object and then manipulate the properties of that object.

This statement refers to the font of a cell range. However, the principles behind it can be applied more generally:

In most situations, you can specify font attributes using VBA by following this simple 2-step process:

Access the object representing the font whose characteristics you want to modify.

This object is, generally, a Font or a Font2 object. I introduce both below.
Manipulate the members (properties and/or methods) of the object you access in step #1 to set the characteristics you want.

The precise VBA constructs that you have available differ depending on whether you’re working with a Font or a Font2 object. I explain several of these constructs in this VBA Tutorial.

This 2-step process is the one I follow when creating the 2 sample macros that appear in the last section of this VBA Tutorial.

I believe that, if you follow this 2-step framework, you’ll find that manipulating fonts with VBA is quite easy. Furthermore, due to the relatively large number of characteristics you can modify, you’ll notice that you have a significant degree of flexibility.

Now that we’ve got this out of the way, let’s look at the VBA constructs you can use to specify font attributes.

VBA Font And Font2 Objects

Font attributes in Excel can, generally, be held by either of the following 2 objects:

Font.
Font2.

Both Font and Font2 contain font attributes of the object you specify.

You generally access both the Font and Font2 objects through different versions of the Font property. In other words, you can call the Font property for several different VBA objects.

The following list includes several versions of the Font property:

The AboveAverage.Font property.
The BulletFormat2.Font property.
The CellFormat.Font property.
The Characters.Font property.
The DataTable.Font property.
The DisplayFormat.Font property.
The FormatCondition.Font property.
The LegendEntry.Font property.
The Phonetic.Font property.
The Phonetics.Font property.
The Range.Font property.
The Style.Font property.
The TableStyleElement.Font property.
The TextRange2.Font property.
The TickLabels.Font property.
The Top10.Font property.
The UniqueValues.Font property.

The behavior of these different versions of the Font property varies to a certain extent. Those differences are, in some cases, substantial. For example:

Most properties above return a Font object. In certain cases (for example, BulletFormat2.Font and TextRange2.Font), the Font property returns a Font2 object.
Some properties above are read only. Others are read-write.

I can’t carry out a detailed analysis of each of these different versions within this blog post. However, I dive deeper into 2 of the most important versions (Range.Font and TextRange2.Font) below.

The basic syntax of the different versions of the Font property is generally as follows:

expression.Font

“expression” represents the object to which you’re referring.

Even though Font and Font2 are similar, they have a few substantial differences. For purposes of this tutorial, the following 3 are particularly important:

As I mention above, the property you use to access one object or the other differs.
The object you work with (Font or Font2) depends on the object whose font attributes you’re specifying. For example, as I show below:
1. If you want to specify the font attributes of a cell range, you work with the Range.Font property. This property returns a Font object.
2. If you’re specifying the font attributes of a chart title, you use the TextRange2.Font property. TextRange2.Font returns a Font2 object.
Font and Font2 have different members. Therefore, the font attributes you can specify when using Font aren’t necessarily the same as those that you can specify when working with Font2.

To further understand the differences between the Font and Font2 objects, let’s take a closer look at the properties of each of these objects.

Properties Of The Font Object

The Font object has the following 18 properties:

Application.
Background.
Bold.
Color.
ColorIndex.
Creator.
FontStyle.
Italic.
Name.
Parent.
Size.
Strikethrough.
Subscript.
Superscript.
ThemeColor.
ThemeFont.
TintAndShade.
Underline.

In the following sections, I explain the main characteristics of properties that are most relevant for purposes of specifying font attributes:

Font.Bold Property

You can use the Font.Bold property to specify whether the font is bold or not, as follows:

Make font bold: Set Font.Bold to True.
Don’t make font bold: Set Font.Bold to False.

The basic syntax of the Bold property is as follows:

expression.Bold

“expression” represents a Font object. Therefore, I simplify the above as follows:

Make font bold:

Font.Bold = True
Don’t make font bold:

Font.Bold = False

Font.Color Property

The Font.Color property allows you to determine the font color.

The basic syntax of Font.Color is as follows:

expression.Color

“expression” represents a Font object. Therefore, I simplify the above as follows:

Font.Color

Theoretically, you specify the color as a decimal value. The palette of available colors has, literally, millions of alternatives for you to choose. Considering this, we can further modify the syntax of the Font.Color property as follows:

Font.Color = Decimal#

“Decimal#” is the decimal value representing the color you want to specify. You can find a list of more than 500 colors, with their name, Hex value, RGB value and decimal value at cloford.com.

However, there’s no real need to keep track of these millions of alternatives and the corresponding decimal values. This is because, when specifying the font color, you can simply rely on the RGB Function.

The RGB Function proceeds as follows:

Allows you to specify the red (R), green (G) and blue (B) components of the color you want to use.
Returns a Long number. This is the decimal value that the Font.Color property uses to determine the font color.

The basic syntax of RGB is as follows:

RGB(red, green, blue)

“red”, “green” and “blue” are numbers between 0 and 255. They represent (respectively) the red, green and blue component of the color you want. If you specify a value larger than 255 for any of the components, Visual Basic for Applications assumes you meant 255.

You can easily find the RGB values that correspond to the color you want to use by, for example:

Using Excel’s Colors dialog box.
Using this RGB Color Codes Chart.

Note that, as explained at the Microsoft Dev Center (in the webpage I link to above):

The RGB color values returned by this function are incompatible with those used by the Macintosh operating system.

Considering all the comments above, you can use the following syntax for purposes of specifying the font color with Font.Color and RGB:

Font.Color = RGB(red, green, blue)

If you’re working with one of the first 8 colors of Excel’s color palette (I explain this further in the following section), you can use the following color names instead of the RGB Function:

vbBlack.
vbWhite.
vbRed.
vbGreen.
vbBlue.
vbYellow.
vbMagenta.
vbCyan.

In this case, you simply set the Color property to the appropriate color name, as follows:

Font.Color = VbColorName

Font.ColorIndex Property

You can use the Font.ColorIndex property to set the font color.

In other words, Font.ColorIndex is an alternative (to Font.Color) for purposes of specifying the font color.

As I explain above, you can use Font.Color to access several million colors.

The Font.ColorIndex property allows you to access the following:

A palette of 56 colors.
The following 2 xlColorIndex constants:
- xlColorIndexAutomatic (-4105), which represents automatic color.
- xlColorIndexNone (-4142), representing no color.

You generally determine the color you want to use by specifying the index value within the applicable palette. Excel MVP Wyman Wong provides a nicely organized palette reference at Access-Excel.tips.

The basic syntax of Font.ColorIndex is as follows:

expression.ColorIndex

“expression” represents a Font object. Considering this and the other comments, I simplify this syntax as follows (for purposes of this tutorial):

Font.ColorIndex = Index#

“Index#” represents the palette index value of the color you’re using.

Font.FontStyle Property

The Font.FontStyle property allows you to set the font style. Font style refers, generally, to the following 2 characteristics:

Bold.
Italic.

The basic syntax of Font.FontStyle is as follows:

expression.FontStyle

“expression” represents a Font object. You specify the appropriate FontStyle as a string.

As I mention above, you generally use FontStyle for purposes of specifying whether a font is bold and/or in italics. Therefore, you can generally use the following syntaxes, depending on the situation you’re in:

No bold, no italics:

Font.FontStyle = “Regular”
Bold, no italics:

Font.FontStyle = “Bold”
Italics, no bold:

Font.FontStyle = “Italic”
Bold and italics:

Font.FontStyle = “Bold Italic”

Font.Italic Property

You can use the Font.Italic property to specify whether the font style is italic or not. You specify this as follows:

Italic: Set Font.Italic to True.
No italic: Set Font.Italic to False.

The basic syntax of Font.Italic is as follows:

expression.Italic

“expression” represents a Font object.

Since Italic can only take the Boolean values of True and False, we can specify the appropriate syntax (depending on the case) as follows:

Italic font:

Font.Italic = True
No Italic:

Font.Italic = False

Font.Name Property

The Font.Name property allows you to specify the actual font you use as a string.

The basic syntax of Font.Name is as follows:

expression.Name

“expression” represents a Font object.

Considering the comments above, I can simplify the syntax of Font.Name as follows:

Font.Name = “FontName”

“FontName” is the name of the font you want to use. This includes, among others, the following:

Arial.
Calibri.
Tahoma.
Times New Roman.
Verdana.

Font.Size Property

You can use the Font.Size property to specify the font size.

The basic syntax of Font.Size is as follows:

expression.Size

“expression” represents a Font object.

Considering the above, I simplify the syntax of Font.Size (for purposes of this tutorial) as follows:

Font.Size = Size#

“Size#” is the font size you want to apply.

Font.Strikethrough Property

The Font.Strikethrough property allows you to specify whether the font is stricken through with a line. You specify this setting as a Boolean:

Strikethrough: Set Font.Strikethrough to True.
No strikethrough: Set Font.Strikethrough to False.

The syntax of Font.Strikethrough is as follows:

expression.Strikethrough

“expression” is a Font object.

Because of the comments above, I can simplify the syntax of the Strikethrough property as follows:

Strikethrough:

Font.Strikethrough = True
No strikethrough:

Font.Strikethrough = False

Font.Subscript Property

You can use the Font.Subscript property to specify whether Excel formats the font as a subscript or not. You specify the value of Font.Subscript as a Boolean:

Subscript: Set Font.Subscript to True.
No subscript: Set Font.Subscript to False.

The syntax of Font.Subscript is as follows:

expression.Subscript

“expression” represents a Font object.

Because of the previous comments, I simplify the syntax above as follows:

Subscript:

Font.Subscript = True
No subscript:

Font.Subscript = False

Font.Superscript Property

The Font.Superscript property is like the Subscript property above. It allows you to determine whether the font is formatted as a superscript or not. You specify this as follows:

Superscript: Set Font.Superscript to True.
No superscript: Set Font.Superscript to False.

The basic syntax of Font.Superscript is as follows:

expression.Superscript

Considering the above, I simplify the syntax of the Superscript property as follows:

Superscript:

Font.Superscript = True
No superscript:

Font.Superscript = False

Font.ThemeColor Property

In previous sections, I explain the Font.Color and Font.ColorIndex properties. Both properties allow you to specify font colors in different ways.

The Font.ThemeColor property allows you to set the font color by referring to the applicable theme color scheme. In other words, this is a third way in which you can determine the color of the relevant font.

The actual colors depend on the color scheme of the workbook. You (or the user) can determine this from the Ribbon > Page Layout > Themes drop-down.

The syntax of Font.ThemeColor is as follows:

expression.ThemeColor

“expression” represents a Font object.

You can easily specify the applicable theme color by using the following xlThemeColor constants:

xlThemeColorAccent1 (5): Accent 1.
xlThemeColorAccent2 (6): Accent 2.
xlThemeColorAccent3 (7): Accent 3.
xlThemeColorAccent4 (8): Accent 4.
xlThemeColorAccent5 (9): Accent 5.
xlThemeColorAccent6 (10): Accent 6.
xlThemeColorDark1 (1): Dark 1.
xlThemeColorDark2 (3): Dark 2.
xlThemeColorHyperlink (11): Hyperlink.
xlThemeColorFollowedHyperlink (12): Followed hyperlink.
xlThemeColorLight1 (2): Light 1.
xlThemeColorLight2 (4): Light 2.

Considering the comments above, I simplify the syntax of the ThemeColor property for purposes of this tutorial as follows:

Font.ThemeColor = xlThemeColorConstant

“xlThemeColorConstant” is the xlThemeColor constant you want to use.

Font.ThemeFont Property

You can use the Font.ThemeFont property for purposes of specifying the font by referring to the applicable theme fonts.

This is an alternative to the Font.Name property I explain above. The basic syntax of the Font.ThemeFont property is as follows:

expression.ThemeFont

“expression” represents a Font object.

You can specify the value of the ThemeFont property by using the xlThemeFont constants, as follows:

xlThemeFontMajor (2): Major theme font.
xlThemeFontMinor (1): Minor theme font.
xlThemeFontNone (0): Don’t use a theme font.

Considering the above, I simplify the syntax of the ThemeFont property for purposes of this tutorial as follows:

Font.ThemeFont = xlThemeFontConstant

“xlThemeFontConstant” is the xlThemeFont constant you want to use.

Font.TintAndShade Property

The Font.TintAndShade property allows you to lighten or darken the font color.

The syntax of Font.TintAndShade is as follows:

expression.TintAndShade

“expression” is a Font object.

The TintAndShade property can take values between -1 (darkest shade) to 1 (lightest shade). If you attempt to enter a value outside of this range, a run-time error occurs.

Considering the above, I simplify the syntax of Font.TintAndShade as follows:

Font.TintAndShade = Value#

“Value#” is a value between -1 and 1, as I explain above.

Font.Underline Property

You can use the Font.Underline property to specify whether Excel underlines the font.

The basic syntax of Font.Underline is as follows:

expression.Underline

“expression” represents a Font object.

The Underline property can take the following xlUnderlineStyle constants as values:

xlUnderlineStyleSingle (2): Single underline.
xlUnderlineStyleDouble (-4119): Double thick underline.
xlUnderlineStyleSingleAccounting (4): 1 closely-placed underline.
xlUnderlineStyleDoubleAccounting (5): 2 thin closely-placed underlines.
xlUnderlineStyleNone (-4142): No underline.

Considering the comments above, the syntax I use for purposes of this Tutorial is as follows:

Font.Underline = xlUnderlineStyleConstant

Properties Of The Font2 Object

The Font2 object has the following properties:

Allcaps.
Application.
AutorotateNumbers.
BaselineOffset.
Bold.
Caps.
Creator.
DoubleStrikeThrough.
Embeddable.
Embedded.
Equalize.
Fill.
Glow.
Highlight.
Italic.
Kerning.
Line.
Name.
NameAscii.
NameComplexScript.
NameFarEast.
NameOther.
Parent.
Reflection.
Shadow.
Size.
Smallcaps.
SoftEdgeFormat.
Spacing.
Strike.
StrikeThrough.
Subscript.
Superscript.
UnderlineColor.
UnderlineStyle.
WordArtFormat.

Let’s take a closer look at the main properties of the Font2 object:

Some Properties Common To The Font And Font2 Objects

In the previous sections, I explain in detail some of the most relevant properties of the Font object. The Font2 object also has some of those properties.

The following are some of these shared properties. The comments I make in previous sections about these properties (applied to the Font object) continue to be generally applicable.

If you go by the information in the Microsoft Dev Center, a slight difference between some of the Font2 properties above (Bold, Italic, Strikethrough, Subscript and Superscript) and their equivalent Font properties is the value that they generally take:

Font2 versions generally take the value of one of the msoTriState constants (msoTrue or msoFalse).
Font versions take a Boolean value (True or False).

In practice, though, there’s usually little difference between the 2. You can generally set the Font2 property versions I mention above to True or False without problems. In my experience, the opposite is usually also true. What I mean is that you can generally set the Font property versions I mention above to msoTrue or msoFalse.

As an example, both of the following statements usually result in Excel formatting the relevant Font or Font2 object with bold:

expression.Bold = msoTrue

expression.Bold = True

“expression” is a Font or Font2 object.

For purposes of this VBA Tutorial, the syntax of the Font2 properties I list above as taking the values of msoTriState constants is as follows:

expression.Bold = msoTriStateConstant

expression.Italic = msoTriStateConstant

expression.Strikethrough = msoTriStateConstant

expression.Subscript = msoTriStateConstant

expression.Superscript = msoTriStateConstant

I explore some other relevant Font2 properties in the following sections.

Font2.Allcaps Property

The Font2.Allcaps property allows you to determine whether the font is formatted as all capital letters. You specify the value of Font2.Allcaps as one of the MsoTriState constants, as follows:

Format as all caps: msoTrue (-1).
Don’t format as all caps: msoFalse (0).

The basic syntax of the Allcaps property is as follows:

expression.Allcaps

“expression” represents a Font2 object.

Some versions of the Font property return a Font2 object. Therefore, considering the comments, above, I simplify the syntax of Font2.Allcaps as follows:

All caps:

Font.Allcaps: msoTrue
Don’t format all as caps:

Font.Allcaps = msoFalse

Font2.BaselineOffset Property

You can use the Font2.BaselineOffset property to specify a horizontal offset for the font.

In practice, setting a BaselineOffset formats the font as subscript or superscript. Whether the text you’re working with becomes a subscript or superscript depends on the value you assign to the property. More precisely:

Negative values result in the text being formatted as a subscript.
Positive values format the text as superscript.

The syntax of Font2.BaselineOffset is as follows:

expression.BaselineOffset

“expression” represents a Font2 object.

Since several versions of the Font property return a Font2 object, I simplify the syntax of the BaselineOffset property as follows:

Font.BaselineOffset = Offset#

“Offset#” is the amount by which the text is offset from the baseline.

Font2.Caps Property

The Font2.Caps property allows you to specify the way in which the text is capitalized. For these purposes, you generally set the Caps property to any of the msoTextCaps constants, as follows:

msoAllCaps (2): All text as caps.
msoNoCaps (0): No caps.
msoSmallCaps (1): All text as small caps.

The basic syntax of the Font2.Caps property is as follows:

expression.Caps

“expression” represents a Font2 object.

Therefore, I simplify the syntax of the Caps property as follows:

Font.Caps = msoTextCapsConstant

Font2.DoubleStrikeThrough Property

You can use the Font2.DoubleStrikeThrough property to specify whether Excel formats the font as double strikethrough or not. The DoubleStrikeThrough property can take either of the following msoTriState constants:

msoTrue (-1): Format as double strikethrough.
msoFalse (0): Don’t format as double strikethrough.

The syntax of Font2.DoubleStrikeThrough is as follows:

expression.DoubleStrikeThrough

“expression” is a Font2 object.

Therefore, depending on what the purpose is, this syntax can be further simplified as follows:

Double strikethrough:

Font.DoubleStrikeThrough = msoTrue
No double strikethrough:

Font.DoubleStrikeThrough = msoFalse

Font2.Equalize Property

The Font2.Equalize property allows you to specify whether Excel equalizes character height. The Equalize property can take the following msoTriState constants:

msoTrue (-1): Equalize character height.
msoFalse (0): Don’t equalize character height.

The basic syntax of Font2.Equalize is as follows:

expression.Equalize

“expression” represents a Font2 object.

Depending on the purpose (equalize height or not), the syntax above can be simplified as follows:

Equalize character height:

Font.Equalize = msoTrue
Don’t equalize character height:

Font.Equalize = msoFalse

Font2.Fill Property

The Font2.Fill property returns a FillFormat object. The members of the FillFormat object allow you to specify the fill formatting for the relevant text.

The FillFormat object has over 30 members. Below are some of the most relevant ones for purposes of this tutorial. I also provide a basic description and the basic syntax of each member.

You can find the whole list of members in the FillFormat object webpage I link to above. Several properties of the FillFormat object are read-only. In most of those cases, you set the corresponding attribute through a method.

FillFormat.OneColorGradient Method

The FillFormat.OneColorGradient method allows you to set the applicable fill to a one-color gradient.

The basic syntax of the OneColorGradient method is as follows:

expression.OneColorGradient(Style, Variant, Degree)

For purposes of the above, the following definitions apply:

expression: A FillFormat object.
Style: Required parameter. Allows you to specify the gradient style using the msoGradientStyle constants.
Variant: Required argument. Allows you to specify the gradient variant, as an Integer from 1 to 4. If you set the Style parameter to msoGradientFromCenter (7), you specify Variant as either 1 or 2 (vs. 1 through 4 for the other Style values).
Degree: Required parameter. Allows you to specify the gradient degree, as a value between 0.0 and 1.0. 0.0 is dark. 1.0 is light

FillFormat.Patterned Method

The FillFormat.Patterned method allows you to set the fill to a pattern. You specify the applicable pattern using the msoPatternType constants.

The syntax of the FillFormat.Patterned method is as follows:

expression.Patterned

“expression” is a FillFormat object.

Considering the comments above, I adjust the above syntax for purposes of this VBA Tutorial as follows:

expression.Patterned msoPatternTypeConstant

FillFormat.PresetGradient Method

The FillFormat.PresetGradient method allows you to set the fill to a preset gradient.

The syntax of the FillFormat.PresetGradient method is as follows:

expression.PresetGradient(Style, Variant, PresetGradientType)

The following definitions are applicable:

expression: A FillFormat object.
Style: Required parameter. You use this argument to specify the gradient style with the msoGradientStyle constants.
Variant: Required parameter. Allows you to specify the gradient variant. The comments I make above (see section about the FillFormat.OneColorGradient method) apply.
PresetGradientType: Required argument. You use this parameter to determine the preset gradient type using one of the msoPresetGradientType constants.

FillFormat.PresetTextured Method

You can use the FillFormat.PresetTextured method to set the fill to a preset texture.

The basic syntax of the PresetTextured method is as follows:

expression.PresetTextured(PresetTexture)

For these purposes, the following definitions apply:

expression: A FillFormat object.
PresetTexture: A required parameter. You use this argument to specify the type of texture using the msoPresetTexture constants.

FillFormat.Solid Method

The FillFormat.Solid method allows you to set a fill to a solid (uniform) color. In other words, you can use the Solid method to convert any of the following fills to a uniform fill:

Gradient.
Textured.
Patterned.
Background.

The basic syntax of the FillFormat.Solid method is as follows:

expression.Solid

“expression” represents a FillFormat object.

FillFormat.TwoColorGradient

You can use the FillFormat.TwoColorGradient method to set a fill to a 2-color gradient.

The basic syntax of the FillFormat.TwoColorGradient method is as follows:

expression.TwoColorGradient(Style, Variant)

The following definitions are applicable:

expression: FillFormat object.
Style: Required parameter. You use this argument to specify the gradient style using the msoGradientStyle constants.
Variant: Required argument. This parameter allows you to specify the gradient variant. Please refer to my comments above (section about the FillFormat.OneColorGradient method) for a description of this parameter.

FillFormat.BackColor And FillFormat.ForeColor Properties

You can use the FillFormat.BackColor and FillFormat.ForeColor properties to access the ColorFormat object. ColorFormat can represent the following colors:

The color of a single-color object.
The foreground or background color of an object that has a gradient or patterned fill.
The color of a pointer.

In the case of the BackColor and ForeColor properties, the ColorFormat object you work with is the following:

FillFormat.BackColor: Background fill color. Applies to shaded or patterned fills.
FillFormat.ForeColor: Foreground fill color (in the case of shaded or patterned fills) or fill color (in the case of solid fills).

The basic syntax of each of these properties is as follows:

FillFormat.BackColor:

expression.BackColor
FillFormat.ForeColor:

expression.ForeColor

The ColorFormat object has several members. For purposes of this tutorial, the following VBA constructs are relevant. In all cases, “expression” (within the syntax) refers to a ColorFormat object.

ColorFormat.Brightness property: Allows you to set the luminosity of the ColorFormat object.

Basic syntax, for purposes of this tutorial, is as follows:

expression.Brightness = Value#

“Value#” is a value between 0.0 and 1.0. 0.0 is dimmest. 1.0 is brightest.
ColorFormat.ObjectThemeColor property: Allows you to set color of ColorFormat object by reference to the applicable theme color scheme.

Basic syntax, for purposes of the topic we’re referring to, is as follows:

expression.ObjectThemeColor = msoThemeColorIndexConstant

“msoThemeColorIndexConstant” is the msoThemeColorIndex constant you choose. This constant specifies the applicable theme color.
ColorFormat.RGB property: You can use the RGB property to set the color of the ColorFormat object. Usually, you do this by using the RGB Function I explain in a previous section.

Considering the comments above, the basic syntax of the ColorFormat.RGB property is as follows:

expression.RGB = RGB(red, green, blue)
ColorFormat.SchemeColor property: The SchemeColor property allows you to set the color of a ColorFormat object by reference to the applicable color scheme palette. This palette is substantially the same as that which applies to the Font.ColorIndex property (which I cover above). However, even though the same colors are available in both palettes, the index values aren’t the same.

More precisely, you can get the appropriate ColorFormat.SchemeColor index value by adding 7 to the Font.ColorIndex value. In other words, for a color:

Font.ColorIndex# + 7 = ColorFormat.SchemeColor#

The basic syntax of the ColorFormat.SchemeColor property is as follows:

expression.SchemeColor = Index#

Index# is the index value of the color you want to use. For these purposes, you can use the Font.ColorIndex palette index values and add 7 (as I explain above).
ColorFormat.TintAndShade property: You can use the ColorFormat.TintAndShade property to lighten or darken the color.

The syntax of ColorFormat.TintAndShade is as follows:

expression.TintAndShade = Value#

“Value#” is a value between -1 (darkest) and 1 (lightest).

FillFormat.GradientAngle Property

The FillFormat.GradientAngle property allows you to set the angle of the gradient fill. You express the angle as a value between 0 and 359.9.

The basic syntax of FillFormat.GradientAngle is as follows:

expression.GradientAngle

“expression” represents a FillFormat object.

Considering the comments above, I simplify the syntax of the GradientAngle property as follows:

expression.GradientAngle = Angle#

“Angle#” is the angle you want to set, expressed as a value between 0 and 359.9.

Font2.Glow Property

You can use the Font2.Glow property to access the GlowFormat object. The GlowFormat object represents the glow effect surrounding an item (in this case, text).

For purposes of this tutorial, the most relevant members of the GlowFormat object are the following. In these cases, “expression” represents a GlowFormat object.

GlowFormat.Color property: Returns a ColorFormat object. This object represents the color of the glowing text.

The basic syntax of the GlowFormat.Color property is as follows:

expression.Color

I introduce the ColorFormat object and several of its main members in the context of the FillFormat.BackColor and FillFormat.ForeColor properties above. Those comments are generally applicable to the ColorFormat object returned by the GlowFormat.Color property.
GlowFormat.Radius property: Allows you to set the radius value of the glow effect.

For purposes of this tutorial, the basic syntax of the Radius property is as follows:

expression.Radius = Radius#

“Radius#” is the radius value you want to use.
GlowFormat.Transparency property: You can use this property to set the degree of transparency for the glow effect.

The syntax of the Transparency property is as follows:

expression.Transparency = Transparency#

“Transparency#” is a value between 0.0 and 1.0. 0.0 is opaque. 1.0 is clear.

Font2.Highlight Property

The Font2.Highlight property returns a ColorFormat object. This ColorFormat object represents the highlight of the relevant font.

The basic syntax of Font2.Highlight is as follows:

expression.Highlight

“expression” represents a Font2 object.

Because of the above, by appropriately working with the members of the ColorFormat object, you can specify the characteristics of the font highlight. I introduce the ColorFormat object and several of its members in the section above (about the FillFormat.BackColor and FillFormat.ForeColor properties). The explanations I provide there are generally applicable to the ColorFormat object returned by Font2.Highlight.

Font2.Kerning Property

You can use the Font2.Kerning property to specify the settings that determine whether Excel applies kerning. For these purposes, you specify a character point size at which Excel begins to apply kerning.

The basic syntax of the Font2.Kerning property is as follows:

expression.Kerning

“expression” is a Font2 object.

Because of the comments above, for purposes of this tutorial, I express the syntax as follows:

Font.Kerning = Points#

“Points#” is the character point size at which Excel begins to apply kerning.

Font2.Reflection Property

The Font2.Reflection property returns a ReflectionFormat object. This ReflectionFormat object represents the reflection format of the relevant text.

By appropriately working with the members of the ReflectionFormat object, you can specify the attributes of the text’s reflection. The following are the main properties you can work with. In all the cases below, “expression” (within the syntax) represents a ReflectionFormat object.

ReflectionFormat.Blur property: Allows you to set the blur of the reflection.

The basic syntax of the Blur property is as follows:

expression.Blur = Blur#

“Blur#” is the amount of blur, measured in points, you want to apply.
ReflectionFormat.Offset property: You can use this property to specify the separation (offset) between the reflection and the text itself.

The basic syntax of the Offset property is as follows:

expression.Offset = Offset#

“Offset#” is the amount of separation, measured in points, you want between the reflection and the text.
ReflectionFormat.Size property: Allows you to specify the size of the reflection.

The basic syntax of ReflectionFormat.Size property, for purposes of this tutorial, is as follows:

expression.Size = Size#

“Size#” is the size of the reflection. This is a value between 0 and 100, representing the percentage size of the reflection (vs. the text).
Reflection.Transparency: Use this property to specify the transparency of the reflection.

The syntax of the Transparency property is as follows:

expression.Transparency = Transparency#

“Transparency#” is the amount of transparency. This is a value between 0 and 1, measuring the percentage of the transparency.

Font2.Shadow Property

The Font2.Shadow property returns a ShadowFormat object. The ShadowFormat object represents the shadow effects for the relevant text.

Therefore, you can use the members of the ShadowFormat object for purposes of specifying the characteristics of the shadow. The following are some of the main members you can work with. For purposes of the following, “expression” (within the syntax) represents a ShadowFormat object.

ShadowFormat.Blur property: Allows you to set the blurriness of the shadow.

The basic syntax of the Blur property is as follows:

expression.Blur = Blur#

“Blur#” is the amount of blur you want to apply.
ShadowFormat.ForeColor property: The ForeColor property returns a ColorFormat object. This object represents the color of the shadow.

I introduce the ColorFormat object in a previous section, where I explain the FillFormat.BackColor and the FillFormat.ForeColor properties. Generally, the comments I provide there are applicable to the ColorFormat object returned by ShadowFormat.ForeColor.
ShadowFormat.IncrementOffsetX method: You can use the IncrementOffsetX method to change the horizontal offset of the shadow. The IncrementOffsetX method (changes the offset by a certain number of points) is different from the OffsetX property (sets the absolute offset) below.

The basic syntax of ShadowFormat.IncrementOffsetX is as follows:

expression.IncrementOffsetX(Increment)

“Increment” is a required parameter. You use this argument to specify the number of points by which the shadow offset changes. Positive numbers move shadow to the right. Negative numbers move the shadow to the left.
ShadowFormat.IncrementOffsetY method: Like the IncrementOffsetX method above. The main difference is that ShadowFormat.IncrementOffsetY allows you to change the vertical (vs. the horizontal) offset of the shadow.

The syntax of the IncrementOffsetY method is as follows:

expression.IncrementOffsetY(Increment)

“Increment” is a required argument. You use this parameter to specify the number of points by which the offset changes.
ShadowFormat.Obscured property: The Obscured property allows you to determine whether the shadow of the text (i) appears filled and (ii) is obscured by the text.

The basic syntax of the Obscured property is as follow:

expression.Obscured

The ShadowFormat.Obscured property can take either of the following MsoTriState constants:
- msoTrue: Shadow appears filled and is obscured by the text.
- msoFalse: Shadow has no fill and is visible through the text (if the text has no fill).
ShadowFormat.OffsetX property: You can use the OffsetX property to set an absolute horizontal offset (in points) of the shadow. Positive OffsetX values result in the shadow being offset to the right of the text. Negative values result in an offset to the left.

The basic syntax of the OffsetX property (for purposes of this blog post) is as follows:

expression.OfssetX = OffsetX#

“OffsetX#” is the offset value you want to use.
ShadowFormat.OffsetY property: The OffsetY property is like the OffsetX property above. Use OffsetY to set an absolute vertical offset (in points) for the shadow.

The syntax of the OffsetY property is like the one of the OffsetX property above:

expression.OffsetY = OffsetY#
ShadowFormat.Size property: The ShadowFormat.Size property allows you to specify the shadow size.

The syntax of ShadowFormat.Size for purposes of this Tutorial is as follows:

expression.Size = Size#

“Size#” is the shadow size you want to specify.
ShadowFormat.Style property: You can use the Style property to set the shadow style.

ShadowFormat.Style generally takes the values of the following msoShadowStyle constants:
- msoShadowStyleInnerShadow: Inner shadow effect.
- msoShadowStyleOuterShadow: Outer shadow effect.
Therefore, for purposes of this Tutorial, the syntax of the Style property is as follows:

expression.Style = msoShadowStyleConstant
ShadowFormat.Transparency property: The Transparency property allows you to specify the degree of transparency of the shadow.

For purposes of this blog post, I use the following syntax:

expression.Transparency = Transparency#

“Transparency#” is the transparency degree you want to set.
ShadowFormat.Type: You can use the ShadowFormat.Type property for purposes of setting the shadow type.

Type takes an msoShadowType constant. msoShadowType constants are determined per their order within the applicable Shadow Style set. For example, the image above displays the msoShadowType constants corresponding to the shadow types that appear within the Text Effects > Shadow drop-down list of the Format contextual tab.

Considering the comments above, I simplify the syntax of the Type property as follows:

expression.Type = msoShadowTypeConstant
ShadowFormat.Visible: The Visible property allows you to determine whether the shadow is visible.

The Visible property can take msoTriState constants msoTrue (-1) or msoFalse (0). Therefore, for purposes of this Tutorial, I use the following statement structure:

expression.Visible = msoTriStateConstant

Font2.Smallcaps Property

The Font2.Smallcaps property allows you to determine whether Excel formats the text using small caps.

Its basic syntax is as follows:

expression.Smallcaps

“expression” is a Font2 object.

The Smallcaps property can take the value of either of the following msoTriState constants:

msoTrue (-1): Small caps.
msoFalse (0): No small caps.

Therefore, you can use the following statements to format (or not) text using small caps:

Small caps:

Font.Smallcaps = msoTrue
No small caps:

Font.Smallcaps = msoFalse

Font2.Spacing Property

You can use the Font2.Spacing property for purposes of specifying the spacing between characters.

Its basic syntax if as follows:

expression.Spacing

“expression” represents a Font2 object.

For purposes of this VBA Tutorial, I use the following syntax:

Font.Spacing = Spacing#

“Spacing#” is the spacing you want to use.

Font2.Strike Property

The Font2.Strike property allows you to specify the strike format Excel uses. The basic syntax of the Strike property is as follows:

expression.Strike

“expression” is a Font2 object. Strike generally takes one of the following the values from within the msoTextStrike enumeration:

msoNoStrike (0): No strike.
msoSingleStrike (1): Single strike.
msoDoubleStrike (2): Double strike.

Considering the above, I can simplify the syntax of the Strike property as follows:

Font.Strike = msoTextStrikeConstant

Font2.UnderlineColor Property

The Font2.UnderlineColor property returns a ColorFormat object that represents the underline color for the text. You can work with the members of the ColorFormat object for purposes of specifying the characteristics of the underline color.

I introduce the ColorFormat object in one of the previous sections. The comments I make there are generally applicable to the object returned by the Font2.UnderlineColor property.

The basic syntax of this property is as follows:

expression.UnderlineColor

“expression” represents a Font2 object.

Font2.UnderlineStyle Property

You can use the Font2.UnderlineStyle property to determine the type of underline style Excel applies.

The syntax of Font2.Underline is as follows:

expression.UnderlineStyle

“expression” is a Font2 object.

The UnderlineStyle property generally takes one of the following values from the msoTextUnderlineType enumeration:

msoNoUnderline (0): No underline.
msoUnderlineWords (1): Underline words.
msoUnderlineSingleLine (2): Single line.
msoUnderlineDoubleLine (3): Double line.
msoUnderlineHeavyLine (4): Heavy line.
msoUnderlineDottedLine (5): Dotted line.
msoUnderlineDottedHeavyLine (6): Heavy dotted line.
msoUnderlineDashLine (7): Dash line.
msoUnderlineDashHeavyLine (8): Heavy dash line.
msoUnderlineDashLongLine (9): Long dashed line.
msoUnderlineDashLongHeavyLine (10): Long heavy line.
msoUnderlineDotDashLine (11): Dot dash line.
msoUnderlineDotDashHeavyLine (12): Dot dash heavy line.
msoUnderlineDotDotDashLine (13): Dot dot dash line.
msoUnderlineDotDotDashHeavyLine (14): Dot dot dash heavy line.
msoUnderlineWavyLine (15): Wavy line.
msoUnderlineWavyHeavyLine (16): Wavy heavy line.
msoUnderlineWavyDoubleLine (17): Wavy double line.

Because of these considerations, I use the following statement structure within this Tutorial:

expression.UnderlineStyle = msoTextUnderlineTypeConstant

Macro Code Examples To Work With Excel VBA Fonts

As I explain above, I can’t cover all the different versions of the Font property within this Tutorial. Therefore, the macro code examples below use the following 2 properties:

Range.Font, which you can use to work with the font of a cell range.
TextRange2.Font, which you can use to work with the text frame within certain objects.

The Excel worksheet I work with in the following examples is shown below. The worksheet includes the following main items:

A table in cells B5 to C35. This table lists how many units of a certain item are sold each day of November 2016.

I use the Range.Font property to determine the font attributes of the column headers in cells B5 and C5.
A bar chart. This chart presents the sales data in the table in #1 above.

I use the TextRange2.Font property to specify the font attributes of this chart’s title.

The purpose of the font formatting actions I carry out in the examples below is simply to illustrate the possibilities you have. My purpose isn’t to argue about the best ways to format table headers or chart titles (although those discussions are certainly valuable). In other words, my exclusive focus is what VBA can do.

Therefore, I am the first to admit that some of the formatting results I show below aren’t particularly beautiful or readable. However, they probably give you a good idea of the following:

The options and flexibility you have when formatting fonts with VBA.
The VBA constructs you use when working with fonts through Visual Basic for Applications.

Before we dive into the actual VBA code examples, let’s look at…

Basic Process To Work With Fonts Using Excel VBA

As I explain at the beginning of this VBA Tutorial, manipulating font characteristics using Excel VBA can feel overwhelming.

After all:

The Excel Object Model generally has 2 different Font objects: Font and Font2.
Despite the above, you access both objects through different versions of the Font property.
To complicate matters further, each of these objects has a long list of members you can work with.

As a general matter, you shouldn’t worry too much. As I mention in the first section of this Tutorial, at a basic level, the process you follow to work with fonts using VBA is composed of 2 simple steps:

Refer to the appropriate Font or Font2 object.
Use the members of the relevant Font or Font2 object to specify the characteristics of the font.

This is the process I follow in the macro examples below. Notice how, despite the amount of VBA constructs that I explain above, the basic structure of the Sub procedures continues to be relatively simple.

Let’s go ahead and check the sample macros:

Example #1: Macro Code To Work With The Font Object

As I mention above, the process you can follow to work with fonts using VBA can be broken in 2 basic steps. The following sections show how I create the first sample macro (Excel_VBA_Font) by following those 2 steps.

Step #1: Refer To The Font Object

This first sample macro manipulates the font of the column headers in the table within the sample workbook:

Therefore, the first thing I do when coding the macro is create a With… End With block. The opening and closing statements of this block are as follows:

With ActiveSheet.Range(“B5:C5”).Font

End With

In this case, this looks as follows:

The following comments explain what these 2 lines of code achieve:

The With… End With block allows us to execute several statements on a certain object.
The object on which the statements are executed is that specified by the With statement.
In this example, the object specified by the With statement is the font of cells B5 and C5 (ActiveSheet.Range(“B5:C5”).Font).

This object reference is built by using the following items:
1. ActiveSheet: The ActiveSheet property returns the active sheet within the active workbook.
2. Range(“B5:C5”): The Worksheet.Range property returns cells B5 and C5 of the active sheet.
3. Font: The Range.Font property returns the font of cells B5 and C5 of the active sheet.
The statements that are executed on font of cells B5 and C5 are those specified in step #2 below.

Let’s move onto…

Step #2: Specify Font Characteristics

Once we set up a reference to the appropriate object (step #1 above), we can start working with the appropriate members of the Font object.

In the following sections, I provide examples of how you can do this for purposes of specifying the main attributes of the font in cells B5 and C5 of the sample worksheet.

Throughout this example, I simply apply the VBA constructs that I explain above.

However, there are cases where you can manipulate a single attribute (for example, color) through different constructs (Font.Color, Font.ColorIndex and Font.ThemeColor). Therefore, in the following sections, I provide several additional comments that may help you decide which construct is more appropriate for your situation and objectives.

More precisely, I will show you how I specify the font characteristics in 5 easy steps, as follows:

Color, tint and shade.
Bold, italic, strikethrough and underline.
Font face.
Size.
Subscript and superscript.

Let’s go through each of these characteristics separately:

Step #1: Specify Font Color (Font.Color, Font.ColorIndex And Font.ThemeColor), Tint And Shade (Font.TintAndShade)

The following 3 properties allow you to specify the font color:

Font.Color.
Font.ColorIndex.
Font. ThemeColor.

Further to the above, you can lighten or darken the shade of a color by using the Font.TintAndShade.

To choose a color, simply add the appropriate statement within the With… End With block created in step #1 above. Let’s look at some examples:

Specify Font Color With The Font.Color Property And The RGB Function

Within the macro example we’re working with, I can add the following statement to make the font color red:

.Color = RGB(255, 0, 0)

The code of the Sub procedure now looks as follows:

The following screenshot shows the results of executing this macro:

Specify Font Color With The Font.ColorIndex Property

An alternative to working with the Font.Color property is to use Font.ColorIndex.

Let’s assume that, in our example, we want to set the font color to blue. The following statement does this:

.ColorIndex = 5

The sample VBA code now looks as follows:

And, as expected, the relevant cells in the sample worksheet now have blue font:

Specify Font Color With The Font.ThemeColor Property

Another option for purposes of specifying font color is the ThemeColor property.

The following statement sets the font color to be equal to Accent 1 of the applicable color scheme:

.ThemeColor = xlThemeColorAccent1

The sample worksheet uses a Theme called “Berlin”. The following screenshot displays the colors of this theme. Notice Accent 1. That’s the color the statement above refers to.

In this case, the sample macro code looks as follows:

The results I obtain when executing this macro are the following:

For purposes of the sample VBA code we’re working with, I leave this line of code. Therefore, this is the statement you’ll see in the following sections.

If you’re working with the sample workbook that accompanies this tutorial, the other sample statements I provide above (with Font.Color and Font.ColorStyle) are commented out (not deleted).

Specify Font Shade

The 3 previous properties (Font.Color, Font.ColorIndex and Font.ThemeColor) deal with the actual font color.

A closely related attribute is the shade (lighter/darker) of the font color. You can control this characteristic by using the Font.TintAndShade property.

In the example we’re working with, I set the font of the column titles to a lighter shade (but not the lightest) by entering the following statement:

.TintAndShade = 0.5

Once I’ve done this, the macro code looks as follows:

Once I execute the current version of the Sub procedure, the sample worksheet looks as follows:

Step #2: Specify Font Style (Font.Bold, Font.FontStyle And Font.Italic), Strikethrough (Font.Strikethrough) And Underline (Font.Underline)

As I show in the previous section, there are several routes you can take for purposes of specifying the font color. To a certain extent, this is also the case when you want to modify the font style (bold or italic).

More precisely, you can specify both font styles (bold and italic) in either of the following ways:

Using their individual properties: Font.Bold or Font.Italic.
Using the Font.FontStyle property. The reason for this is that FontStyle can refer to both styles: bold and italic.

In addition to making the font bold or italic, you can apply strikethrough formatting or underline it. For these purposes, you work (respectively) with the Font.Strikethrough and Font.Underline properties.

Let’s look at some sample statements that allow you to further determine the font format within our example:

Specify Bold And Italic Font Style With The Fond.Bold And Font.Italic Properties

The Font.Bold and Font.Italic properties take the Boolean values True (Bold/Italic) and False (no Bold/Italic).

Let’s apply this knowledge to the macro example we’re working with. In this case, I add the following lines of code to make the font both bold and italic:

.Bold = True

.Italic = True

The new version of the sample macro (Excel_VBA_Font) looks as follows:

The following screenshot shows the results I obtain when executing this macro. Notice that Excel applies both bold and italic formatting.

For purposes of the sample macro we’re creating, I leave these 2 statements in (vs. the one below with the FontStyle property). If you’re working with the workbook that accompanies this tutorial, the line of code with the FontStyle property (below) is commented out.

Specify Bold And Italic Font Style With The Font.FontStyle Property

The FontStyle property doesn’t take Boolean values. You specify the applicable style as a string.

In the case of the sample macro we’re working with, I add the following sample statement to achieve the same results as with the Font.Bold and Font.Italic properties above:

.FontStyle = “Bold Italic”

In other words, this statement also makes the font both bold and italic. In this case, the VBA code looks as follows:

The results I obtain when executing the macro are, however, the same as those I show above.

Specify Strikethrough

You can specify whether font is stricken through with a line by using the Font.Strikethrough property.

In the sample macro we’re working with, I add the following line of code to add a strikethrough:

.Strikethrough = True

The current version of the VBA code looks as follows:

When I execute the macro, Excel adds strikethrough formatting (as expected).

Specify Underline

The Font.Underline property allows you to determine whether Excel underlines the text or not.

In the sample macro we work with, I set Underline to xlUnderlineStyleDoubleAccounting. To do this, I add the following line of code:

.Underline = xlUnderlineStyleDoubleAccounting

The sample macro now looks as follows:

And the result of executing the Sub procedure is that Excel adds 2 thin closely-placed underlines to the text:

Step #3: Specify Font Face (Font.ThemeFont And Font.Name)

Font face is another font characteristic that you can manipulate through more than 1 property. More precisely:

You generally specify the font face you want to use by using the Font.Name property.
As an alternative, you can use the Font.ThemeFont property to specify the font face by referring to the applicable theme fonts.

Let’s look at how each of these options looks within a VBA Sub procedure:

Specify Font Face With The Font.Name Property

When you use the Font.Name property, you can specify any font you want to use as a string.

Therefore, within the context of the sample macro we’re working with, I add the following statement to choose Verdana font:

.Name = “Verdana”

At this point, the sample macro code looks as follows:

The results I obtain upon executing this sample macro are as follows. Notice that, as expected, the font of the headers is Verdana.

Specify Font Face With The Font.ThemeFont Property

The ThemeFont property is more restrictive than the Name property I explain above. The reason for this is that Font.ThemeFont only allows you to choose from the applicable theme fonts.

In the sample workbook that accompanies this blog post, the theme I use is called “Berlin”. The font used by the Berlin theme is Trebuchet MS.

In the example macro, I include the following statement to convert the font to the major theme font (Trebuchet MS in this case):

.ThemeFont = xlThemeFontMajor

After I include this statement in the sample macro, the VBA code looks as follows:

The following image shows the results I obtain after executing the macro. Notice that Excel sets the font back to Trebuchet MS (major font of the theme I’m using).

For purposes of moving forward in the process of developing the sample macro, I leave this statement in (vs. that with the Font.Name property above). Within the sample workbook that accompanies this Tutorial, both statements are available but that with FontName is commented out.

Step #4: Specify Font Size (Font.Size)

As I explain above, you can specify font size by using the Font.Size property.

In the macro example we’re working in, I add the following statement:

.Size = 13

At this point, the sample macro code looks as follows:

The following screenshot shows the results I obtain when executing this macro. Notice that, as expected, the font size is 13.

Step #5: Specify Subscript Or Superscript (Font.Subscript And Font.Superscript)

The Font.Subscript and Font.Superscript properties allows you to specify whether Excel formats a font as subscript or superscript.

Within the context of the sample macro we’re working with, it doesn’t make much sense to format the text as either subscript or superscript. After all, we’re working with the headers of a table.

On the other hand, I don’t have a need to set these properties to False (no subscript/superscript). These headers aren’t formatted as subscript or superscript by default

Despite the above, and for illustration purposes, I add the following 2 statements to the sample macro. These statements set Font.Subscript and Font.SuperScript to False. As a consequence, the text isn’t formatted as either. If the text was previously subscript or superscript, these statements format the font as normal text.

.Subscript = False

.Superscript = False

The final version of the sample macro looks as follows:

The sample worksheet, however, doesn’t change (vs. the previous step).

This brings us to the end of the macro code example that works with the Font object. Let’s move onto the next example:

Example #2: Macro Code To Work With The Font2 Object

For purposes of creating this second sample macro, I follow the same 2-step process I describe in other sections above:

Refer to the appropriate Font2 object.
Use the members of the Font2 object to specify its characteristics.

The following sections describe how I go about this for purposes of creating a sample macro (Excel_VBA_Font2). Let’s dive right in:

Step #1: Refer To The Font2 Object

This second sample macro works with the title of the chart within the sample workbook:

Just as I did in the previous sample macro #1, I begin by creating a With… End With block. In this case, the 2 lines of code are as follows:

With ActiveSheet.ChartObjects(1).Chart.ChartTitle.Format.TextFrame2.TextRange.Font

End With

The code of the newly created macro (Excel_VBA_Font2) looks as follows:

These 2 lines achieve the following:

By using a With… End With block, we can execute several statements on an object.
The object on which the statements are executed is the one we specify in the opening With statement.
As I mention above, the object we want to work with is the chart title (ActiveSheet.ChartObjects(1).Chart.ChartTitle.Format.TextFrame2.TextRange.Font).

I build the object reference by using the following VBA constructs:
1. ActiveSheet: The ActiveSheet property returns the current active sheet.
2. ChartObjects(1): ChartObjects returns the embedded chart identified by the index number 1.
3. Chart: The ChartObject.Chart property returns a Chart object.
4. ChartTitle: The Chart.ChartTitle property returns the title of the relevant chart.
5. Format: The ChartTitle.Format property returns a ChartFormat object. In this case, we use this object to access the formatting of the chart title.
6. TextFrame2: The ChartFormat.TextFrame2 property returns a TextFrame2 object.
7. TextRange: The TextFrame2.TextRange property returns a TextRange2 object.
  
  The result of the above is that we get access to the chart title’s text frame.
8. Font: The TextRange2.Font property returns the Font2 object the rest of the macro works with.

I’m aware that this object reference is complex. In general, the part of Excel’s object model that deals with charts is relatively complex.

However, working with charts using VBA isn’t the topic of this VBA Tutorial. Therefore, I suggest you don’t worry too much about the length of the object reference above. I’m using it because the chart title is a prominent Font2 object within the sample worksheet.

I may, however, write about Excel’s chart object model in future blog posts. If you want to be informed whenever I publish new content in Power Spreadsheets, please register for our Newsletter by entering your email address below.

Let’s move back to the topic that interests us within this VBA Tutorial:

Fonts.

Step #2: Specify Font Attributes

This is the step where we can work with the members of the Font2 object to determine the font characteristics.

In the following sections, I provide examples of how you can do this to specify the main attributes of the chart title font in the sample workbook.

All the VBA statements I describe below are simply applications of the VBA constructs that I cover above.

In some cases, you can manipulate a single characteristic (for example, caps) through different constructs (Font2.Allcaps, Font2.Smallcaps and Font2.Caps). Therefore, I add several comments that may help you choose the best way to proceed to craft a macro that works for you.

To do this, I show you how I specify the font attributes in 8 easy steps, as follows:

Fill.
Glow, highlight, reflection and shadow.
Bold, italic, strike and underline.
Font face.
Size.
Caps and character height equalization.
Character spacing.
Subscript, superscript and offset.

All the sample statements below are relatively simple. They follow the basic structure and syntax that I describe in the first part of this VBA Tutorial. Therefore, I provide limited comments.

If you need further information about any construct from the examples below, please refer to the relevant section above.

Let’s dive right in…

Step #1: Specify Font Fill Attributes (Solid, Gradient, Pattern, Texture, Color)

If you’re working with a Font object (as in example #1 above), you can specify font color, tint and shade.

If you’re dealing with a Font2 object, you have many more options. In the following sections, I provide examples of how you can work specify the following types of font fill attributes:

Gradient fill.
Pattern fill.
Texture fill.
Solid fill.
Fill colors.

In the following sections, I show individual examples of each of the main constructs you can work with. However, for purposes of the further development of the macro example, I work with a Solid fill.

In other words, the VBA code that I use in steps #2 to #8 below uses the FillFormat.Solid property.

If you’re following along with the sample workbook that accompanies this Tutorial, the relevant module has all the sample statements. However, the ones that I don’t use in other steps are commented out.

All the properties I mention above are members of the FillFormat object. Therefore, before we start looking at the statements that implement these properties, we need to create a reference to FillFormat.

For these purposes, we can use the Font2.Fill property. Fill returns the FillFormat object the sample statements below work with. In the context of the sample macro, I add a nested With… End With block. The 2 lines of code are as follows:

With .Fill

End With

The VBA code now looks as follows:

Let’s start adding statements to the new With… End With block.

Specify Gradient Fill

Excel allows you to work with different types of gradient fills. The following sample statements show some ways in which you can do this:

Specify 1-Color Gradient.

The OneColorGradient method allows you to specify a single-color gradient fill.

For purposes of our example, I add the following VBA statement to the With… End With block:

.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, Degree:=1

The resulting VBA code looks as follows:

The following screenshot shows the results I get when executing the macro above:

Specify 2-Color Gradient.

The TwoColorGradient method allows you to specify a 2-color gradient fill.

To apply a 2-color gradient within our example, I use the following statement:

.TwoColorGradient Style:=msoGradientFromCenter, Variant:=2

In this case, the VBA code looks as follows:

And the results I obtain when executing the macro version above are the following. The code above doesn’t set any colors. Therefore, the text has the same color as in the previous example. I illustrate how you can modify colors (with FillFormat.BackColor and FillFormat.ForeColor) further below.

Specify A Preset Gradient.

You can use FillFormat.PresetGradient to set the font fill to a preset gradient.

For purposes of the sample macro, I add the following statement:

.PresetGradient Style:=msoGradientVertical, Variant:=1, PresetGradientType:=msoGradientEarlySunset

The code of the Sub procedure now looks as follows:

The font of the chart title looks as follows once I execute the macro above:

Specify Angle Of Gradient Fill.

The methods I cover in the previous sections allow you to choose the type of gradient fill. The FillFormat.GradientAngle property allows you to specify the angle of the gradient fill.

For purposes of our sample macro, I add the following statement:

.GradientAngle = 180

For purposes of this explanation, I also use the statement that sets a 1-color gradient (explained above). The resulting macro code looks as follows:

The following screenshot shows the results I get when executing this Sub procedure. Notice the difference vs. the consequences of using the OneColorGradient method alone (above).

Specify Patterned Fill

You can use the FillFormat.Patterned method to set the font fill to a pattern.

Considering the above, I enter the following line of code within our sample Sub procedure:

.Patterned msoPatternZigZag

The new version of the sample macro code looks as follows:

The following screenshot shows what happens when I execute this version of the macro:

Specify Textured Fill

The FillFormat.PresetTextured method allows you to set the font fill to a preset texture.

For purposes of our macro example, I add the following statement:

.PresetTextured msoTextureSand

The resulting VBA code looks as follows:

And the results I obtain when executing this macro version are as shown below:

Specify Solid Fill

In the most basic scenario, you simply specify that the font fill is a uniform (solid) color. The FillFormat.Solid method allows you to do this.

In the sample macro, I implement this by entering the following line of code:

.Solid

I also add an additional statement specifying the color (.ForeColor.ObjectThemeColor = msoThemeColorAccent1). I explain how this statement works in the following section.

So that the resulting VBA Sub procedure looks as follows:

As expected, Excel formats the chart title font with a single color:

For purposes of the sample Sub procedure we’re creating, I use this type of fill. Therefore, in the following steps, you only see this statement (not the other examples below). If you’re working with the sample workbook that accompanies this VBA Tutorial, sample statements that I don’t use in the next steps are commented out (but they’re still there).

Specify Fill Colors

The FillFormat.BackColor and FillFormat.ForeColor properties allow you to access the color attributes of the font you’re working with.

I provide a thorough explanation of the BackColor and ForeColor properties, as well as the ColorFormat object they return, above. Please refer to the relevant section for further information.

Here, I show you an example of how the FillFormat.ForeColor property allows you to specify the fill color of a solid fill. I do this because of the following reasons:

As I explain above, in this sample macro #2, the chart title font is set to have a solid fill.
Both the BackColor and the ForeColor properties return a ColorFormat object. Therefore, you can easily modify the sample code I provide below to apply it to the ColorFormat object returned by the FillFormat.BackColor property.

In the code samples I show below, I modify the following aspects of the font color:

Color, through the following constructs:
- The ColorFormat.ObjectThemeColor property.
- The ColorFormat.RGB property, alongside the RGB function.
- The ColorFormat.SchemeColor.
Luminosity, by using the ColorFormat.Brightness property.
Tint and shade, with the ColorFormat.TintAndShade property.

To keep the VBA code tidy, I use an additional With… End With block. All the statements within this block deal with the ColorFormat object returned by the ForeColor property. The empty block looks as follows:

Since I can modify the color through 3 different properties (like in macro example #1 above), let’s start by looking at these examples. In the list below, I show (i) the statements I enter in the sample VBA code, (ii) the resulting version of the sample VBA code, and (iii) the results I get when executing the applicable macro version.

ColorFormat.ObjectThemeColor:
1. Statement: Notice that the following statement is the one that I include in the previous section (when adding “.Solid”).
  
  .ObjectThemeColor = msoThemeColorAccent1
2. VBA Code:
3. Results:
ColorFormat.RGB:
1. Statement:
  
  .RGB = RGB(0, 255, 0)
2. VBA Code:
3. Results:
ColorFormat.SchemeColor:
1. Statement:
  
  .SchemeColor = 15
2. VBA Code:
3. Results:

For purposes of moving forward with the development of the sample macro, I use the statement that works with the ObjectThemeColor property (#1 above). If you’re following along with the sample workbook that accompanies this blog post, I leave all the statements within the VBA code. The ones that I don’t use are commented out.

Let’s move on to the luminosity of the font. For these purposes, I enter the following statement:

.Brightness = 0.5

Now, the macro code looks as follows:

And the results I get when executing the macro are shown in the image below:

Finally, let’s deal with the tint and shade. In this case, I add the following statement to the sample macro:

.TintAndShade = 0.5

The following image shows the new version of the VBA code:

And the following screenshot shows the results I get when executing the macro above:

Step #2: Specify Glow (Font2.Glow), Highlight (Font2.Highlight), Reflection(Font2.Reflection) And Shadow (Font2.Shadow)

In this step, I show you some of examples of how you can apply the following 4 text effect attributes:

Glow.
Highlight.
Reflection.
Shadow.

Let’s start by looking at how you can…

Specify Glow

The Font2.Glow property gives you access to the GlowFormat object. This object holds the attributes of the glow effect surrounding the text.

I provide a thorough description of the main members of the GlowFormat object above. In this example, I work with the following VBA constructs:

GlowFormat.Color property, alongside the ColorFormat.RGB property and RGB Function.

GlowFormat.Color returns a ColorFormat object representing the color of the glow. I use the ColorFormat.RGB property and RGB function to set that color.
GlowFormat.Radius property.
GlowFormat.Transparency property.

Let’s put these properties into practice.

Since the 3 properties above work with the same object (GlowFormat), I start by creating a With… End With block within the sample macro. This looks as follows:

I then fill the block with the properties I want to work with. The following list shows the statements I add to the sample code:

GlowFormat.Color:

.Color.RGB = RGB(0, 0, 255)
GlowFormat.Transparency:

.Transparency = 0.8
GlowFormat.Radius:

.Radius = 5

The resulting macro code now looks as follows:

The results I get when executing the macro version above are the following:

Specify Highlight

The Font2.Highlight property gives you access to a ColorFormat object.

I provide examples of working with ColorFormat objects in other sections of this VBA Tutorial. Therefore, in this case, the example I provide deals with only 1 of the members of the ColorFormat object: ColorFormat.RGB. More precisely, I use the ColorFormat.RGB property along the RGB Function for purposes of specifying a highlight color.

The statement that I add to the sample macro is as follows:

.Highlight.RGB = RGB(255, 0, 0)

The resulting macro looks as follows:

And the results I obtain when executing the Sub procedure are displayed in the following image:

The highlight makes the changes I make below (reflection and shadow) less visible. Therefore, to make it easier for you to see the consequences of the VBA statements I add, I don’t use highlight from now on. If you check out the sample workbook that accompanies this Tutorial, the statement above continues to be there. I just commented it out.

Specify Reflection

The Font2.Reflection property returns a ReflectionFormat object. The object holds the characteristics of the reflection format for the text.

In this case, I work with the following 4 properties of ReflectionFormat:

ReflectionFormat.Blur.
ReflectionFormat.Offset.
ReflectionFormat.Size.
ReflectionFormat.Transparency.

Let’s see how these properties can be included in the sample macro:

First, since all of them work with the ReflectionFormat object returned by Reflection, I add a With… End With block as follows:

Once the With… End With block is created, I fill it with the statements that appear in the following list:

ReflectionFormat.Transparency:

.Transparency = 0.5
ReflectionFormat.Size:

.Size = 100
ReflectionFormat.Offset:

.Offset = 5
ReflectionFormat.Blur:

.Blur = 5

The resulting macro code looks now as follows:

The following are the results I get when executing this macro version:

Specify Shadow

The Font2.Shadow property returns a ShadowFormat object. You can use this object to access the attributes of the font’s shadow effects.

For purposes of the macro example we’re working on, I use the following properties of the ShadowFormat object:

Type.
Visible.
Style.
Blur.
OffsetX.
OffsetY.
ForeColor.
Transparency.
Size.

I add these VBA constructs to the sample macro by following the same process as with the Font2.Glow and Font2.Reflection properties in previous sections:

I add a With… End With block to ensure that all properties refer to the appropriate Font2 object. The following image shows how this looks like:
I add the statements that work with the properties I describe above to the With… End With block

In this case, I add the following statements:

.Type = msoShadow28

.Visible = msoTrue

.Style = msoShadowStyleOuterShadow

.Blur = 4

.OffsetX = -3

.OffsetY = -3

.ForeColor.RGB = RGB(0, 0, 0)

.Transparency = 0.5

.Size = 200

The new version of the sample macro code looks as follows:

The following screenshot shows the results I get when executing this Sub procedure:

Step #3: Specify Bold (Font2.Bold), Italic (Font2.Italic), Strikethrough (Font2.Strikethrough, Font2.DoubleStrikethrough, Font2.Strike) And Underline (Font2.UnderlineStyle, Font2.UnderlineColor)

As I explain above, the Font object has properties that allow you to specify whether Excel applies the following formats to a certain piece of text:

Bold.
Italic.
Strikethrough.
Underline.

You can also manipulate these characteristics when working with a Font2 object.

Certain properties (Bold and Italic), work in a very similar way regardless of whether you’re dealing with a Font or a Font2 object. However, as I show below, this isn’t the case with strikethrough and underline-related properties.

Let’s take a more detailed look at each of these characteristics:

Specify Bold And Italic

As I mention above, the following properties work in a very similar way:

Bold and Font2.Bold.
Italic and Font2.Italic.

Therefore, I add the following statements to the sample macro to apply both bold and italics to the text:

.Bold = msoTrue

.Italic = msoTrue

The following image shows how the VBA code looks like:

The following screenshot shows the results I get when executing this macro version:

Specify Strikethrough

When working with a Font2 object, you can use the 3 following properties for purposes of specifying the strikethrough:

Strikethrough.
DoubleStrikethrough.
Strike.

Let’s look at how you can work with each of these in the context of the sample macro:

Font2.Strikethrough.

The Font.Strikethrough and Font2.Strikethrough properties work in a very similar way. The similarities between these 2 properties mirror those that I mention above for the Font.Bold/Font2.Bold and Font.Italic/Font2.Italic properties.

Therefore, I add the following statement to the sample macro code:

.Strikethrough = msoTrue

The now-current version of the VBA code looks as follows:

The results I obtain when executing this macro version are shown in the image below:

This is the statement that I leave within the sample macro code for purposes of its further development below.

In other words, I only show the code with the DoubleStrikethrough and Strike properties in the 2 sections below (but not later). If you use the sample workbook that accompanies this VBA Tutorial, the code of the VBA Sub procedure includes all the statements. The ones I don’t use (DoubleStrikethrough and Strike) are commented out.

Font2.DoubleStrikethrough.

You can work with the Font2.DoubleStrikethrough property in a similar way as you’d work with the Strikethrough property above.

Because of this, I add the following statement to the sample macro to apply double strikethrough. Notice how, structurally, this line of code is the same as that with the Font2.Strikethrough property above:

.DoubleStrikeThrough = msoTrue

The following screenshot shows the sample macro code:

And this following image displays the results I obtain when executing this macro:

Font2.Strike Property

The third property you can use to specify a font strikethrough is Font2.Strike.

As I explain in the appropriate section above, the Strike property takes its values from the msoTextStrike enumeration.

Therefore, for purposes of the example we’re working with, I add the following line of code to specify a double strike:

.Strike = msoDoubleStrike

Now, the current version of the VBA code looks as follows:

The results I obtain when executing this Sub procedure are displayed in the following image:

Specify Underline Style And Color

When you’re working with a Font2 object, you can specify the following 2 characteristics separately:

Style with the Font2.UnderlineStyle property.
Color with the Font2.UnderlineColor property.

Each of these properties works differently. Let’s look at each of them separately:

Font2.UnderlineStyle.

If you’re dealing with a Font2 object, Excel allows you to choose from a relatively wide variety of underline styles.

For example, I specify a wavy double line underline by including the following line of code in the Sub procedure:

.UnderlineStyle = msoUnderlineWavyDoubleLine

The resulting VBA code looks as follows:

The results I get when executing the macro above are shown in the following image:

Font2.UnderlineColor.

The UnderlineColor property returns a ColorFormat object. Therefore, you specify the color attributes of the underline by appropriately working with the members of ColorFormat.

I provide several comments and examples of how to work with the ColorFormat object in other sections of this VBA Tutorial. The most detailed comments and examples are in the sections where I deal with the FillFormat.ForeColor property. Please refer to those items for a more detailed description.

In this example, I only work with one of the properties of the ColorFormat object: RGB. More precisely, I set the underline color of the Font2 object by using the ColorFormat.RGB property and the RGB Function.

Therefore, I add the following statement to the sample macro:

.UnderlineColor.RGB = RGB(0, 255, 0)

The resulting macro version looks as follows:

The results I get when the macro above is executed are shown in the following image:

Steps #4 And #5: Specify Font Face (Font2.Name) And Size (Font2.Size)

I provide a list of properties that are common to the Font and Font2 objects above. Both the Name and Size properties are included in this list. These 2 properties allow you to do the following:

Name: Specify the font face.
Size: Set the font size.

I add the following statements to the sample macro. Notice that these are the same lines of code I add in steps #3 (Font.Name) and #4 (Font.Size) of the previous macro example #1.

.Name = “Verdana”

.Size = 13

The new version of the sample macro code #2 looks as follows:

The results I get when executing this Sub procedure appear in the following image. Notice that, as expected, the font is Verdana size 13.

Step #6: Specify Caps (Font2.Allcaps, Font2.Smallcaps, Font2.Caps) And Character Height Equalization (Font2.Equalize)

Strictly speaking, in this step #6 I deal with 2 slightly different font characteristics:

Whether the text is capitalized. You can specify this attribute through the Allcaps, Smallcaps or Caps properties.
Whether Excel equalizes the height of all the characters. For these purposes, you use the Equalize property.

In the following sections, I provide separate examples of how to use each of the properties I list above.

Specify Caps With The Font2.Allcaps And Font2.Smallcaps Properties

From a general point of view, you can use the Allcaps and Smallcaps properties to specify whether Excel formats the text as follows:

All capital letters, with Font2.Allcaps.
Small caps, with Font2.Smallcaps.

The following bullet points show how I implement the above properties when developing the sample macro:

Allcaps:
1. Statement:
  
  .Allcaps = msoTrue
2. VBA Code:
3. Results:
Smallcaps:
1. Statement:
  
  .Smallcaps = msoTrue
2. VBA Code:
3. Results:

For purposes of the future development of the sample macro, I work with the Font2.Smallcaps property. Therefore, the chart title I work with is in small caps.

I delete all the other VBA statement samples I cover within this step #6 after showing how they work. If you’re using the sample workbook that accompanies this VBA Tutorial, all the statements are there. The ones that I don’t use in further steps are commented out.

Specify Caps With The Font2.Caps Property

You can see the Font2.Caps property is an alternative to the Font2.Allcaps and Font2.Smallcaps above.

For example, I add the following statement to the sample macro code. This formats the text with small caps (just as the Smallcaps property).

.Caps = msoSmallCaps

The VBA code looks as follows:

As shown by the following image, the results I get when executing this macro version are substantially the same as those I get when using the Font2.Smallcaps property above.

Specify Font Height Equalization

The Font2.Equalize property doesn’t precisely deal with capitalization (as the other properties above). What Font2.Equalize allows you to do is specify that the height of all relevant characters is the same

In the context of the sample macro, I add the following statement to the code:

.Equalize = msoTrue

The now-current version of the macro looks as follows:

And here are the results I get when executing the Sub procedure:

Step #7: Specify Spacing (Font2.Spacing)

To modify the character spacing, you can work with the Spacing property.

Therefore, I include the following line of code in the sample macro:

.Spacing = 3

The now-current version of the sample macro is displayed in the following image:

And the consequences of executing this Sub procedure are as follows:

Step #8: Specify Subscript (Font2.Subscript), Superscript (Font2.Superscript) Or Offset (Font2.BaselineOffset)

The Subscript and Superscript properties are included in the list of properties that work substantially similar regardless of whether you’re working with a Font or a Font2 object.

Therefore, for the most part, the explanation and example I provide in step #5 of the previous sample macro #1 apply to this step #8.

When it comes to the macro sample we’re working with, as is the case in step #5 of the previous macro example #1:

It doesn’t make much sense to format the text of the chart title as either subscript or superscript.
Strictly speaking, I don’t need to set these properties to msoFalse. This is because the chart title isn’t formatted as subscript or superscript anyway.

However, for illustrative purposes, I add the following statements to the sample macro:

.Subscript = msoFalse

.Superscript = msoFalse

The following image shows the new version of the VBA code:

As expected, the results I obtain when executing the macro are the same as those in the previous step #7:

Specify Baseline Offset

As I explain above, you can specify a horizontal offset of the relevant font by using the BaselineOffset property.

For example, I can include the following statement in the sample macro:

.BaselineOffset = 0.5

This makes the VBA code look as follows:

And the results that I obtain when executing the macro are shown below:

However, as I mention above, I don’t want to format the chart title as a superscript (nor as subscript). Therefore, for the final version of the VBA code, I delete this line of code. The following image shows the last (and complete) version of the sample macro code:

If you’re working with the sample workbook that accompanies this Tutorial, the statement I explain above appears as a comment.

The following image shows the final version of the sample worksheet. This is after I execute the Sub procedure above. However, as expected, there aren’t any changes vs. the results obtained after step #7 above.

Conclusion

Visual Basic for Applications allows you to specify several different font characteristics. After reading this VBA Tutorial, you have the information and resources you need to work with these attributes.

More precisely, in this blog post, you’ve read about the following topics:

What is the simple 2-step process that your macros can follow for purposes of modifying font characteristics.
What are the Font and Font2 objects, as well as what are several VBA properties that return these objects.
What are the members of the Font and Font2 objects, and how you can use them.

Furthermore, we’ve created 2 comprehensive macro examples that work with the Font and Font2 objects (respectively). The accompanying step-by-step explanations show how you can easily craft VBA Sub procedures that manipulate and determine virtually any font characteristic. The formatting results aren’t necessarily beautiful or readable. But, as I mention above, my purpose is to show you the following:

The options and flexibility you have when formatting fonts with VBA.
The VBA constructs you can use to achieve the formatting you want.

Remember that you can get immediate free access to the Excel workbook that accompanies this VBA Tutorial by clicking here. This workbook includes the complete code of the 2 sample macros above, as well as the worksheet that the macros modify.

Before you leave, please take a few seconds to leave a comment below and let us know: Which font characteristics do you commonly modify with VBA?

Books Referenced In This Excel VBA Tutorial

Excel VBA Programming For Dummies.