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:
- Example 1, Change Color of All Borders in Range
- Example 2, Change Color of Some Border Edges of Range
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:
After pressing the select color button a color dialog will open:
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:
After choosing the color to apply using the color dialog, only the edges selected will be modified:
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:
- Excel VBA, Border Style and Thickness
- Excel VBA, Create Border
- Excel VBA, Format Cells and Ranges Using the Macro Recorder
- Excel VBA, Remove Border
- Excel VBA, Get Border Properties
- Excel VBA Borders
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