Font Properties in VBA – Color, Size, Style

In the previous article, we learned to change the background color of the cells using VBA. Today we will learn to change the font properties of cells with the help of VBA. We will learn different methods to change the font color of cells, apply font bold, font italic, and underline, We will learn to change cell style with the help of VBA.

You will enjoy this article. So let us begin.

VBA Font Property

We use the Font property in VBA with the Range object. This enables to access different properties associated with the text in the range.

For instance, you can see the list of font properties as follows.

Range("<range?").Font.<Font_Property>

Change Font Color using VBA

There are a number of ways to change the font color of cells in VBA. The different methods are:-

• Using Color Constants
• Using RGB Function
• Using ColorIndex Property

We will learn each of the methods.

Color Constant to Change Font Color

We can assign the font color to the range with the help of the Color property of the Font property. There are different color contestants available in VBA to assign different colors.

You can use the following Sub procedure to test different font colors using color constants.

Sub fontColorConstants()

'different font color constants in vba
'********************************

Range("A1").Font.Color = vbBlack
Range("A1").Value = "Black"

Range("A2").Font.Color = vbRed
Range("A2").Value = "Red"

Range("A3").Font.Color = vbGreen
Range("A3").Value = "Green"

Range("A4").Font.Color = vbYellow
Range("A4").Value = "Yellow"

Range("A5").Font.Color = vbBlue
Range("A5").Value = "Blue"

Range("A6").Font.Color = vbMagenta
Range("A6").Value = "Magenta"

Range("A7").Font.Color = vbCyan
Range("A7").Value = "Cyan"

End Sub

Using RGB Function to Change Font Color

We can use the RGB function and specify the three color components to use the newly formed color as the font color of the range.

Range("<range>").Font.Color = RGB(<red>,<green>,<blue>)

For instance, you can change the font color of the range A1:C10 as follows.

Sub ex1RGBFontColor()

'change font color of range A1:A10 using RGB function
'****************************************************

Range("A1:A10").Font.Color = RGB(215, 20, 220)
Range("A1:A10").Value = "Font"

'bold the text
'****************************************************
Range("A1:A10").Font.Bold = True

End Sub

Change Font Color using the ColorIndex property

The value of the ColorIndex property can lie between 1 and 56. It has predefined colors associated with each index. You can use the following Sub procedure to see different colors of the ColorIndex property.

Sub ShowColorPallete()
'generate color palelte using ColorIndex property
Dim i As Integer
Dim j As Integer
Dim x As Integer
x = 1
For i = 1 To 7
    For j = 1 To 8
        Cells(i, j).Font.ColorIndex = x
        Cells(i, j).Value = x
        Cells.EntireColumn.AutoFit
        x = x + 1
    Next j
Next i
End Sub

Change Font Size Using VBA

You can change the font size of cells with the help of the Size property of the Font. For instance, we can change the font size of the range A1:A5 with the following Sub Procedure.

We are using the InputBox function to input the value of font size from the user.

Sub changeFontSize()

'change font size using VBA
'******************************

'input the value of font size
'******************************
x = InputBox("Enter font size")

'assign to font size property
'******************************
Range("A1:A5").Font.Size = x

End Sub

Bold, Italic or Underline Text using VBA

We can use the Bold, Italic, or Underline property of Font and set it to True to apply.

You can use the following lines of code to apply bold, italic, or underline on a range of cells.

ActiveCell.Font.Bold = True
ActiveCell.Font.Italic = True
ActiveCell.Font.Underline = True

You can use any Range instead of activeCell.

The toggle option for bold, itlaic or underline can be made as follows.

ActiveCell.Font.Bold = Not (ActiveCell.Font.Bold)
ActiveCell.Font.Italic = Not (ActiveCell.Font.Italic)
ActiveCell.Font.Underline = Not(ActiveCell.Font.Underline)

Change Font Name of Text

Use the Name property to change the font name of the range.

Range(<range>).Font.Name="Arial"

Range(<range>).Font.Name="Meta Dahiya"

Range(<range>).Font.Name="Comic Sans"

Change Cell Style

We can use the Style property of the Range object to use different cell styles from the following styles in Excel.

For example, we can apply the text style Good to the range A1:A10.

Sub changeCellStyle()

'changing cell style to Good
'********************************
Range("A1:A10").Style = "good"
Range("A1:A10").Value = "GOOD"

End Sub

This is how we change font properties using VBA.

Thank you for reading. ❤