Excel VBA Set Underline (Sample Code + Download)

In this article I’ve provided an example on how you could use VBA to set underlines for cells and ranges.

There are 4 different underline style the user can select from:

  • Single
  • Double
  • Single Accounting
  • Double Accounting

For more information about the different underline types and their associate VBA code please see Excel VBA, Underline Font Style.

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

There is some text written in row 2. The user selects an underline style from the drop down lists in row 1. When the user makes a selection the underline style will change in row 2:

Excel VBA, Set Underline Style

Excel VBA, Set Underline Style Single In the figure below the user is selecting an underline style for cell C2 from the drop down list in cell C1:

Excel VBA, Set Underline Style C2 After making the selection the underline style is update in the cell below:

Excel VBA, Set Underline Style Double The drop down lists are created using data validation. For more information about creating drop down lists in excel please see Excel VBA Drop Down Lists. The data for the drop down lists are stored in sheet 2:

Excel VBA, Drop Down List Data 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 columns
For i = 1 To 5
'No underline
If Cells(1, i) = "None" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlNone
'single underline
ElseIf Cells(1, i) = "Single" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlSingle
'double underline
ElseIf Cells(1, i) = "Double" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlDouble
'single accounting
ElseIf Cells(1, i) = "Single Accounting" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlSingleAccounting
'double accounting
ElseIf Cells(1, i) = "Double Accounting" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlDoubleAccounting
End If
Next i
End Sub

The event handler loops through the columns and makes the necessary changes:

For i = 1 To 5
...
next i

The If statement below checks if the user selected “None” from the drop down list:

If Cells(1, i) = "None" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlNone

The If statement below checks if the user selected the Single underline style from the drop down list:

ElseIf Cells(1, i) = "Single" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlSingle

The If statement below checks if the user selected the Double underline style from the drop down list:

ElseIf Cells(1, i) = "Double" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlDouble

The If statement below checks if the user selected the Single Accounting underline style from the drop down list:

ElseIf Cells(1, i) = "Single Accounting" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlSingleAccounting

The If statement below checks if the user selected the Double Accounting underline style from the drop down list:

ElseIf Cells(1, i) = "Double Accounting" Then
Range(Cells(2, i), Cells(2, i)).Font.Underline _
= xlDoubleAccounting

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

Leave a Reply

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