Excel VBA Border Colors

One of the things you probably want to modify when working with borders in VBA for Excel is the border color. Previously I’ve explained about colors in the article VBA Excel Colors. Therefore I’m assuming readers are familiar with the basic concept of working with colors before reading this article.

Jump To:

 

You can download the file and code for this example here.


Example 1, Change Color of All Borders in Range

In the program below when the user presses the “Select Color” button a color dialog will appear asking the user to select a color. For more information about color dialogs please see Excel VBA, Color Dialog xlDialogEditor. After selecting a color the color of the border in the range F7:M18 will be changed accordingly:

Excel VBA, Border Color
After pressing the select color button a color dialog will open:

Excel VBA, Color Dialog, Border
Result:

Excel VBA, Color, Border, Result
The code used in this example can be seen below:

'executes when the button is clicked
Private Sub btnColor_Click()
Dim intResult As Integer
'displays a color dialog
intResult = Application.Dialogs(xlDialogEditColor).Show(10)
'changes the color of the borders in the range F7:M18
Range("F7:M18").Borders.Color = ThisWorkbook.Colors(10)
End Sub

The main function used in this example is a button_click event handler. The event handler executes when the user presses the "Select Color" button:

Private Sub btnColor_Click()
...
End Sub

The line below displays the color dialog and prompts the user to select a color:

intResult = Application.Dialogs(xlDialogEditColor).Show(10)

The line below applies the selected color to all the borders in the range F7:M18.

-

Example 2, Change Color of Some Border Edges of Range:

This example is similar to the previous example with the exception that the user can select the edges to apply the color change to:

Excel VBA, Example 2, Border, Color, Edge Selector

After choosing the color to apply using the color dialog, only the edges selected will be modified:

Excel VBA, Example 2, Border, Color, Edge Selector, Result
The drop down list is created using data validation. For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists. The code used in this program can be seen below:

'executes when the button is clicked
Private Sub btnColor_Click()
Dim intResult As Integer
'displays a color dialog
intResult = Application.Dialogs(xlDialogEditColor).Show(10)
'checks if the left edge border was selected
If Cells(5, 1) = "Left Edge" Then
Range("C7:J18").Borders(xlEdgeLeft).Color = _
ThisWorkbook.Colors(10)
'checks if the top edge border was selected
ElseIf Cells(5, 1) = "Top Edge" Then
Range("C7:J18").Borders(xlEdgeTop).Color = _
ThisWorkbook.Colors(10)
'checks if the bottom edge border was selected
ElseIf Cells(5, 1) = "Bottom Edge" Then
Range("C7:J18").Borders(xlEdgeBottom).Color = _
ThisWorkbook.Colors(10)
'checks if the right edge border was selected
ElseIf Cells(5, 1) = "Right Edge" Then
Range("C7:J18").Borders(xlEdgeRight).Color = _
ThisWorkbook.Colors(10)
'checks if the inside vertical border was selcted
ElseIf Cells(5, 1) = "Inside Vertical" Then
Range("C7:J18").Borders(xlInsideVertical).Color = _
ThisWorkbook.Colors(10)
'checks if the inside horizontal border was selected
ElseIf Cells(5, 1) = "Inside Horizontal" Then
Range("C7:J18").Borders(xlInsideHorizontal).Color = _
ThisWorkbook.Colors(10)
'checks if the diagonal down border was selected
ElseIf Cells(5, 1) = "Diagonal Down" Then
Range("C7:J18").Borders(xlDiagonalDown).Color = _
ThisWorkbook.Colors(10)
'checks if the diagonal up border was selected
ElseIf Cells(5, 1) = "Diagonal Up" Then
Range("C7:J18").Borders(xlDiagonalUp).Color = _
ThisWorkbook.Colors(10)
End If
End Sub

The If statements check which edge the user has selected from the drop down lists:

If Cells(5, 1) = "Left Edge" Then
...
ElseIf Cells(5, 1) = "Top Edge" Then
...
End If

The line below changes the color for the left edge of the range C7:J18:

Range("C7:J18").Borders(xlEdgeLeft).Color = ThisWorkbook.Colors(10)

 

You can download the file and code for this example 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 *

More To Explore