In this article I will explain how you can change the size of the font for a cell or range in Excel using VBA. I’ve also provided an example on how you can retrieve the size of the font used in a cell or range.

Jump To:

• Basics
• Example 1, Set Font Size
• Example 2, Get Font Size

You can download the code and file related to this article here.

Basics:

The following line of code changes the size of the font used in cell A1 to l8:

Range("A1").Font.Size = 18


Before:

After:

The line below retrieves the size of the font used in cell A1 and prints the value in cell B1:

Cells(1, 2) = Range("A1").Font.Size


Result:

Example 1, Set Font Size:

In this example there are drop down lists in row 2. The user selects a font size from them. Upon selecting a value from the drop down lists the size of the font in row 1 is updated:

After the user selects 14 from the drop down list the size of the font in cell D1 is changed to 14:

The drop down lists are created using data validation. The source for the data validation is in sheet 2:

For more information about using 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 new value from
'the drop down lists
Private Sub worksheet_change(ByVal target As Range)
Dim i As Integer
'loops through the cells in row 1
For i = 1 To 4
    'adjusts the font of the cells in row 1 to
    'the font selected in row 2
    Range(Cells(1, i), Cells(1, i)).Font.Size _
    = Cells(2, i)
Next i
End Sub


Example 2, Get Font Size:

The example below gets the font size used in a cell. There is text written in row 1. When the user presses the Run button their size is printed in row 2:

After changing the font size of the cells in row 1 and pressing the Run button:

The code used in this program can be seen below:

'executes when the user presses the Run button
Private Sub btnRun_Click()
Dim i As Integer
'loops through the cells in row 1
For i = 1 To 4
    'gets the font size
    Cells(2, i) = Range(Cells(1, i), _
    Cells(1, i)).Font.Size
Next i
End Sub


You can download the code and file related to this article here.

See also:

• VBA, Excel Font Formatting
• Excel VBA, Formatting Cells and Ranges Using the Macro Recorder

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com