In this article I will explain how you can use VBA to add strikethrough to the font of cells and ranges. I’ve also provided a sample code which checks if a cell has the strikethrough font or not.

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

Jump To:

• Basics
• Example 1, Set Strikethrough
• Example 2, Get  Strikethrough Status

Basics:

The code below adds a strikethrough to the font in cell A1:

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


Before:

After:

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

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


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

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


False:

True:

Example 1, Set Strikethrough:

In this example there is some text written in row 1. There are also drop down lists in row 2 with the value Yes and No. When the user changes the value of a drop down list in row 2 to true, a  strikethrough will be added to the font in row 1. When the user changes the value of the drop down list back to No, the strikethrough will be removed from the cell in row 1:

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
        'adds a strikethrough to the cell in row 1
        Range(Cells(1, i), Cells(1, i)).Font.Strikethrough _
        = True
    Else
        'removes the strikethrough from the cell in row 1
        Range(Cells(1, i), Cells(1, i)).Font.Strikethrough _
        = False
    End If
Next i
End Sub


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

Private Sub worksheet_change(ByVal target As Range)


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 adds a strikethrough to the font in the cell in row 1:

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


The code below removes the strikethrough from the font in the cell in row 1:

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


After selecting the “Yes” value from the drop down list a strikethrough is added to the font in cell D1:

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  Strikethrough 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 a strikethrough 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
    'strikethrough property
    If Range(Cells(1, i), Cells(1, i)).Font.Strikethrough = 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 strikethrough property or not:

If Range(Cells(1, i), Cells(1, i)).Font.Strikethrough = 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 strikethrough:

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

You can download the file and code 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