Excel VBA, Font Subscript

Aug 25, 2015 by azurous in Excel

In this article I will explain how you can use VBA to change the font style in a cell or range to subscript. I’ve also provided a sample code which checks if the font in a cell is subscript or not.

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

Jump To:

Basics
Example 1, Set Subscript
Example 2, Get Subscript Status

–

Contents

Basics:

The code below changes the font in cell A1 to subscript:

Range("A1").Font.Subscript = True

Before:

After:

The following line of code removes the subscript from the font in cell A1:

Range("A1").Font.Subscript = False

The following line of code prints the current subscript status of the font in cell A1 in cell B1:

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

False:

True:

–

Example 1, Set Subscript:

In this example there is some text written in row 1. There are also drop down lists in row 2 with the values Yes and No. When the user changes the value of a drop down list in row 2 to True, the font style in row 1 will be changed to subscript. When the user changes the value of the drop down list back to No, the subscript font in row 1 will be changed back to normal:

Private Sub worksheet_change(ByVal target As Range) Dim i As Integer 'iterates through the cells in row 1 For i = 1 To 4 If Cells(2, i) = "Yes" Then 'changes the font in row 1 to subscript Range(Cells(1, i), Cells(1, i)).Font.Subscrip _ = True Else 'removes the subscript property from the font in 'row 1 Range(Cells(1, i), Cells(1, i)).Font.Subscript _ = False End If Next i End Sub

The code below iterates through the cells in row 1:

For i = 1 To 4 ... End Sub

The If statement checks if the user selected “Yes” or “No” from the drop down lists:

If Cells(2, i) = "Yes" Then

The code below changes the font property of the cell in row 1 to subscript:

Range(Cells(1, i), Cells(1, i)).Font.Subscript _ = True

The code below removes the subscript font style from the font in cell D1:

Range(Cells(1, i), Cells(1, i)).Font.Subscript _ = False

After selecting the “Yes” value from the drop down list the font style in cell D1 is changed to subscript:

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

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

–

Example 2, Get Subscript Status:

In this example when the user presses the Run button, the program will loop through the cells in row 1. If the font in the cell has the subscript style the cell in row 2 will be colored green:

'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 'checks if the font in the cell in row 1 has the 'subscript property If Range(Cells(1, i), Cells(1, i)).Font.Subscript = True Then 'changes the color in the cell in row 2 to green Range(Cells(2, i), Cells(2, i)).Interior.Color = 3394611 Else 'removes the color from the cell in row 2 Range(Cells(2, i), Cells(2, i)).Interior.Color = xlNone End If Next i End Sub

The main function is the Button_Click event handler which executes when the user presses the Run button::

Private Sub btnRun_Click()

The code below loops through the cells in row 1:

For i = 1 To 4 ... Next i

The If statement checks if the cell in row 1 has the subscript effect or not:

If Range(Cells(1, i), Cells(1, i)).Font.Subscript = True Then

The line below changes the fill color of the cell in row 2 to green. The number 3394611 is a color code. The color code was found using the macro recorder. For more information on this topic please see Excel VBA, Getting the Color Code Using the Macro Recorder.

Range(Cells(2, i), Cells(2, i)).Interior.Color = 3394611

The line below removes the fill color of the cell in row 2:

Range(Cells(2, i), Cells(2, i)).Interior.Color = xlNone

In the figure below none of the cells in row 1 have the subscript effect:

After adding the subscript effect to some of the cells and pressing the Run button:

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