Excel VBA, Font Name

Jul 12, 2015 by azurous in Excel

In this article I will explain how you can change the font (name) used in a cell or range. I’ve also provided a sample code which gets the current font (name) used in a cell or range.

You can download the code and file used in this article here.

Jump To:

Basics
Example 1, Set Font
Example 2, Get Font

–

Contents

Basics:

Using the code below you can change the font (name) used in cell A1 to “Arial”:

Range("A1").Font.Name = "Arial"

–

Example1, Set Font:

In sheet 2 there is a list of available font styles:

In row 1 of sheet 1, drop down lists are created which the user is able to select a font name from:

In the figures below the user selects the Vineta BT font for cell C2:

The drop down lists are created using data validation. The values for the data validation are in sheet2:

For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists.

The program uses a Worksheet_Change event handler. The event handler executes when the user selects a new value from the drop down lists:

'executes when the user selects a value from 'the drop downn list Private Sub Worksheet_Change(ByVal target As Range) Dim i As Integer 'loops through the columns in sheet1 For i = 1 To 5 'changes the font of the cell in row 2 to the font 'selects in row 1 Range(Cells(2, i), Cells(2, i)).Font.Name = Cells(1, i) Next i End Sub

The code below loops through the five columns:

For i = 1 To 4 ... Next i

The line below changes the font of the cell in row 2 to the font (name) selected in row 1:

Range(Cells(2, i), Cells(2, i)).Font.Name = Cells(1, i)

–

Example 2, Get Font:

In this example there is text written in row 2. When the user presses the Run button, in row 1 the font (name) used in the cell below ( row 2) is printed:

By changing the font (name) for the cells in row 2 and pressing the run button the values in row 1 are updated:

The program uses a Button_Click event handler. The event handler executes when the user presses the Run button:

'executes when the user clicks the Run button Private Sub btnRun_Click() Dim i As Integer 'loops through the three columns For i = 1 To 3 'prints the font name in row 1 Cells(1, i) = Range(Cells(2, i), _ Cells(2, i)).Font.Name Next i End Sub

The code below loops through the three columns:

For i = 1 To 3 ... Next i

The code below prints the name of the font used in row 2 in row 1:

Cells(1, i) = Range(Cells(2, i), _ Cells(2, i)).Font.Name

You can download the code and file used in this article here.