How to Use Excel VBA Border ColorIndex Property

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA is a powerful programming language that allows you to automate tasks in Excel. We can format cells and ranges, and add borders to cells or ranges. Borders can be applied to cells or ranges using different properties in VBA, such as you can use Excel VBA border colorindex property to color borders.

The ColorIndex property allows you to specify a color for the border using an index number corresponding to a particular color in the current color palette. The index numbers range from 1 to 56 and correspond to different colors, including black, white, red, green, blue, and many others. You can easily color the border using VBA codes in Excel.


How to Open VBA Macro Editor in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. You can follow these simple steps to open the VBA editor too.

Steps:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Launching Visual Basic Editor from the Developer Tab

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write the VBA code.

Selecting a New Module from the Visual Basic Editor Window


 Excel VBA Border ColorIndex Property (3 Examples)

In this article, we will explain the ways to use Excel VBA border ColorIndex. Here, we will show four examples with codes and proper illustrations to help you understand the process. Here the dataset contains the names of some customers, the products they purchased, and information on the date and amount of the purchase.

Sample Dataset


Example 1: Set a Border Color to a Table Range Using ColorIndex 

VBA code to Set a Border Color to a Table Range Using ColorIndex

In the above image, you can see the VBA codes to set a border color to a table using ColorIndex. Copy the following VBA codes and paste them into your Module.

Sub Table_Border_Color()
    ' Set the border color of a table range to orange
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    tbl.Range.Borders.ColorIndex  = 32
End Sub

VBA Breakdown

Sub Table_Border_Color()
  • First, we will begin the code with the user-defined sub-procedure named Table_Border_Color. It will perform a series of operations when we run the module.
Dim tbl As ListObject
  • Here, the Dim statement is to declare a variable name ‘tabl’ and the type of this variable is ListObject.
Set tbl = ActiveSheet.ListObjects("Table1")
  • Then, the Set command is used to assign a reference to an object. The object in this case is a ListObject representing the table named “Table1” on the active sheet. The “ActiveSheet” property returns the currently active sheet, and the “ListObjects” property returns a collection of ListObjects on the sheet.
tbl.Range.Borders.ColorIndex  = 32
  • After that, The “tbl.Range” property returns a reference to the table’s range of cells. The “Borders” property returns a collection of range borders. The “ColorIndex “ attribute specifies the color index for the borders. And the value 32 means the ColorIndex number 32 which returns the color orange.
End Sub
  • Finally, this End command finishes the VBA macro.

Applying Border Color in a Table.

Here, you can easily change the border color of a table by inserting a particular color index number in the code.


Example 2: Apply Conditional Border Color in a Range

VBA code to Apply Conditional Border Color in a Range

In this example, we will show how to color a range of cells depending on a condition. So, we will do conditional formatting using the VBA border ColorIndex in Excel. You can copy the following code and paste it into your module.

Sub Conditional_Border_Color()
    ' Set the border color of a range based on a condition
    Dim cell As Range
    For Each cell In Range("E5:E12")
        If cell.Value > 500 Then
            cell.Borders.ColorIndex  = 46
    End If
    With cell.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            'Set border style to continuous
            .Weight = xlMedium
            'Set border weight to medium
            End With
    With cell.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
    End With
   Next cell
End Sub

VBA Breakdown

Sub Conditional_Border_Color()
    ' Set the border color of a range based on a condition
    Dim cell As Range
  • First, we will create a sub-procedure named Conditional_Border_Color.
  • Then, the Dim statement declares a variable called “cell” and sets its data type to Range.
For Each cell In Range("E5:E12")
  • Now, this For statement will start a loop that will iterate over each cell in the range “E5:E12”. The For loop will assign the current cell to the “cell” variable, so we can modify it inside the loop.
If cell.Value > 500 Then
            cell.Borders.ColorIndex  = 46
End If
  • Here, then, the if statement checks if the value in the current cell, is greater than 500.
  • If the value in the cell is greater than 500, this line sets the border color of the cell to color index 46. This will change the color of the borders of the cell.
  • The End If ends the If statement.
With cell.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            'Set border style to continuous
            .Weight = xlMedium
            'Set border weight to medium
           End With
  • The “With” block in VBA enables the application of various settings to an object without repeatedly referencing the item. In this instance, we are applying settings to the cell’s left border.
  • Then, the .LineStyle = xlContinuous makes the line style of the left border continuous. This will make the left border of the cell a solid line.
With cell.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            End With
    Next cell
End Sub
  • This With block will do the same as above for the right border.
  • And finally, move to the next cell. After all the cells are covered, it will finish the sub-procedure.

Applying Conditional Border Color in a Range.

We can change the border color of a particular range depending on a specific condition. Here, you can see that the code changes the color of the cells where the product price is above 500$.


Example 3: Set a Cell Border Color Based on Another Cell Format

VBA code to Set a Cell Border Color According to Another Cell Format

With the code above, we will color the border of a range according to the interior color of a particular cell. We will use the border ColorIndex in Excel VBA to do that.

Sub SetBorderColor()
    Dim cell1 As Range
    Dim cell2 As Range
    Set cell1 = Range("C8") 'Change the range to the cell that contains the format you want to use
    Set cell2 = Range("B5:E12") 'Change the range to the cell you want to apply the border color to
    'Apply border color based on the format of cell1
    With cell2.Borders
        .LineStyle = xlContinuous
        .Color = cell1.Interior.Color
        .Weight = xlThin
    End With
End Sub

VBA Breakdown

Sub SetBorderColor()
    Dim cell1 As Range
    Dim cell2 As Range
  • First, we create a module and name the sub-procedure SetBorderColor.
  • Then, write two Dim statements to declare two Range variables named cell1 and cell2.
Set cell1 = Range("C8") 'Change the range to the cell that contains the format you want to use
Set cell2 = Range("B5:E12") 'Change the range to the cell you want to apply the border color to
  • Here, the line
    Set cell1 = Range("C8")

    sets the value of the “cell1” variable to the Range object representing cell C8 on the active sheet. This cell contains the format (interior color)  that will be used to apply a border color to the other cells.

  • And,
    Set cell2 = Range("B5:E12")

    sets the value of the “cell2” variable to the Range object representing the range of cells from B5 to E12 on the active sheet. This is the range of cells that will have their borders colored based on the format of cell1.

With cell2.Borders
        .LineStyle = xlContinuous
        .Color = cell1.Interior.Color
        .Weight = xlThin
    End With
  • Here, the line
    .Color = cell1.Interior.Color

     will change the border color of the cell2 range to the color of the cell1 range.

Setting Border Color According to Cell Color

In the image above, you can see that we have changed the border color of the table according to cell C8.

Read More: How to Use Excel VBA Borders Linestyle Property


How to Use RGB Codes Instead of ColorIndex Property on Border with Excel VBA

VBA code to Use Custom RGB Color in Border

We will apply the above VBA codes to set the border color in a range using the RGB color code in Excel VBA. Here, you can use any number between 0 and 255 for the red, green, and blue components to color the border in Excel VBA.

Sub Custom_RGB_BorderColor()
    ' Set the border color of a range using a custom RGB color
    Dim r As Integer, g As Integer, b As Integer
    r = 255 ' red component
    g = 128 ' green component
    b = 0   ' blue component
    Range("B5:E12").Borders.Color = RGB(r, g, b)
End Sub

VBA Breakdown

Sub Custom_RGB_BorderColor()
  • At the beginning, we will name the subprocedure as Custom_RGB_BorderColor.
Dim r As Integer, g As Integer, b As Integer
  • After that, we will use the Dim statement to declare three variables r,g, and b all as integer-type variables. These variables will hold the red, green, and blue components of the custom RGB color that will be used for the border.
r = 255 ' red component
g = 128 ' green component
b = 0   ' blue component
  • Then, we write the value of r,g,b variables. You can set the value to any number between 0 and 255. Here, 255 represents the maximum value for the red component of an RGB color. And 0 represents the minimum value for the blue component of an RGB color.
Range("B5:E12").Borders.Color = RGB(r, g, b)
  • In this line, we specify the border color of the range B5:E12 to a custom RGB color using the RGB() function. The RGB() function takes three arguments: the red, green, and blue components of the color. In this case, the values of the “r”, “g”, and “b” variables are passed as arguments to the RGB() function.

Alt Text: Using Custom RGB Border Color in a RangeHere, in the above chart, the code changes the border color of the cells according to RGB (255, 128, 0) codes.


How to Create List of Colors Based on ColorIndex Property in Excel VBA

VBA code to Set ColorIndex List.

The above VBA code will show a few color index-based colors in Excel. There are 56 colorIndex for pre-defined colors in the Excel color palette. You can use them to color the borders in Excel VBA.

Sub ColorIndex ()
    Dim i As Integer
    For i = 5 To 28 'Loop from 1 to 10
      Cells(i, 3).Interior.ColorIndex  = i
      'Print the number in column A
    Next i
End Sub

VBA Breakdown

Sub ColorIndex ()
  • First, we name a sub-procedure ColorIndex.
 Dim i As Integer
  •    Then, write a Dim statement to introduce an Integer type variable i.
For i = 5 To 28 'Loop from 1 to 10
      Cells(i, 3).Interior.ColorIndex  = i
      'Print the number in column A
Next i
  • After that, we will write a For loop that will iterate from 5 to 28. The variable i is used to keeping track of the current iteration of the loop.
  • Next,  i will change starting from 5 to 28 and the code will color a cell consecutively according to the ColorIndex.
End Sub
  •  Finally, the End Sub will end the code.

Color Index Chart.

This chart contains the colors for the color index number 1 to 10. You can choose from any of the 56 colors from the index.


How to Set Border Weight of a Range with Excel VBA

VBA code to Set Border Weight of a Cell with Excel VBA

We will change the border weight in Excel VBA using the above VBA code.

Sub SetBorderWeight()
    Range("B4:E12").Borders.Weight = xlThick
End Sub

VBA Breakdown

Range("B4:E12").Borders.Weight = xlThick

  • We select the range B4:E12 and make the border weight thick using the xlThick statement.

Changing Border Weight in a range

Here, we used xlThick in the VBA code. As a result, it will increase the border weight of the selected range.


How to Set Interior Color in Excel VBA

VBA code to Set Interior Colors in Excel VBA

With the above code, we will change the interior colors in Excel VBA. We will use the ColorIndex object to do that. That means you can color any of the 56 colors in the index to change the background cell color.

Sub Interior_ColorIndex ()
    Range("B5:E12").Interior.ColorIndex  = 34
End Sub

VBA Breakdown

Range("B5:E12").Interior.ColorIndex  = 34
  • First, we access the Interior property of the range B5:E12 with  Range(“B5:E12”).Interior
  • Then, ColorIndex  = 34 changes the color of the range with a specific color from the Excel color palette, which corresponds to the color index value of 34.

Applying Interior Color in a Range

You can easily color the dataset according to a particular color index.


How to Set Theme Color in Excel VBA

VBA code to Set Theme Color in Excel VBA

Sub Set_Theme_Color()
    Range("B5:E12").Interior.ThemeColor = xlThemeColorAccent3
End Sub

VBA Breakdown

Range("B5:E12").Interior.ThemeColor = xlThemeColorAccent3
  • Firstly, we call the Interior property of the range B5:E12 with Range(“B5:E12”).Interior.
  • Then, we select the property ThemeColor from the Interior object.
  • Finally, we select the Themecolor to xlThemeColorAccent3, which is a constant value defined in Excel VBA that represents an accent color from the current Excel theme.

Setting Theme Color in a Range

In the above image, you can see that we can color a particular range according to preset theme colors.


How to Clear Border Colors in Excel VBA

VBA code to  Clear Border Colors in Excel VBA

Now, here is a VBA code to clear the border color in a range in Excel VBA. You can apply the code to clear any of the above-applied border colors in Excel VBA.

Sub Clear_Border_Color()
    Dim cell As Range
    For Each cell In Selection
        With cell.Borders
            .LineStyle = xlContinuous 'Set border style to continuous
            .ColorIndex  = xlAutomatic 'Clear border color
            .Color = 0 'Set border color to black
        End With
    Next cell
End Sub

VBA Breakdown

Sub Clear_Border_Color()
  • First, this VBA macro begins with the sub-procedure Clear_Border_Color.
Dim cell As Range
  • Next, we put the Dim statement to declare a variable named cell of type Range to represent each cell in the selection.
For Each cell In Selection
  •  Then, we will write a For loop that will iterate through each cell in the selection.
 With cell.Borders
  • Here, under the With block, we can apply formatting style for cell borders.
.LineStyle = xlContinuous 'Set border style to continuous
            .ColorIndex  = xlAutomatic 'Clear border color
            .Color = 0 'Set border color to black
End With
  • The code above makes the borderline continuous and xlAutomatic takes the border color to default.
  • Finally, the .color=0 makes the border color black.
Next cell
  • This line means the for loop will color the Next cell up to the last cell in Range.
End Sub
  • Finally, this End Sub ends the code.

Download Practice Workbook

You can download the practice Excel workbook from the download button below.


Conclusion

In this article, we showed four examples of using the Excel VBA border ColorIndex property. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Maruf Hasan
Maruf Hasan

Maruf Hasan, a BSc Electrical & Electronic Engineering graduate from Ahsanullah University of Science & Technology, boasts over a year of service as an Excel & VBA Content Developer at Exceldemy. He authored 30+ insightful articles and offers solutions to diverse Excel challenges. Maruf's exceptional content reflects his passion for Microsoft Office, problem-solving, and writing. Committed to simplifying complex processes, he significantly contributes to Exceldemy and is deeply enthusiastic about continuous learning in Microsoft Office Suite and data... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo