How to add borders all around an Excel range with a simple VBA macro

When recording a macro to see what is the VBA code to apply borders, you probably see the following code:

            With Selection.Borders(xlEdgeLeft)

                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With

When you simply want to have border all around, you can shorten the code to:

            With Selection.Borders

                .LineStyle = xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = xlThin

            End With

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to change a shape color in Excel using a VBA macro

Change Excel shape interior color and border color using VBA ColorIndex

VBA:

Sub change_shape_color()
     Dim shp As Shape
     With ActiveSheet.Shapes("My_shape").OLEFormat.Object
         Select Case .Interior.ColorIndex
             Case 3
                 .Interior.ColorIndex = 43 'light green
                 .Border.ColorIndex = 10 'dark green
             Case 43
                 .Interior.ColorIndex = 45 'light orange
                 .Border.ColorIndex = 46 'dark orange
             Case Else
                 .Interior.ColorIndex = 3 'light red
                 .Border.ColorIndex = 30 'dark red
         End Select
     End With
 End Sub

Excel:

Assign macro to Excel shape: Right click > Assign_Macro... and select the macro "change_shape_color" written above.

Created by DoMyExcel.com

Customized Excel Solutions starting at $29!

Code syntax: charles.racaud.free.fr