Excel VBA, Font Size

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:

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:

Excel VBA, Font Size Before
After:

Excel VBA, Font Size 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:

Excel VBA, Font Size Get, 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:

Excel VBA, Font Size Set
After the user selects 14 from the drop down list the size of the font in cell D1 is changed to 14:

Excel VBA, Font Size Set 14
The drop down lists are created using data validation. The source for the data validation is in sheet 2:

Excel, VBA, Data Validation Source
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:

Excel VBA, Get Font Size
After changing the font size of the cells in row 1 and pressing the Run button:

Excel VBA, Get Font Size After
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:

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

One thought on “Excel VBA, Font Size”

  1. Naveen says:

    Good thing

Leave a Reply

Your email address will not be published. Required fields are marked *