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.
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 fontsize 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 fontsize 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.