Border Around in Excel Cells using VBA (Visual Basic for Applications)

Border Around in Excel Cells using VBA (Visual Basic for Applications)

BorderAround

Method applies border around cells in given range in a sheet. Border Around is very useful method which helps decorating the data to enhance look and feel. In this post I am going to put Border Around each cells in a Selection or Range with the help of VBA code.

Syntax

expression.BorderAround(LineStyles, Weight, ColorIndex, Color, ThemeColor)

Parameters

  1. LineStyle: refers to border which user wants to put around a cell. XlLineStyle Enum provides following constants which can be applied to set Line Style:
    • xlDash
    • xlContinuous
    • xlDashDot
    • xlDashDotDot
    • xlDot
    • xlDouble
    • xlLineStyleNone
    • xlSlantDashDot
  2. Weight: to set the weight of the border it takes XlBorderWeight enum which offers following constants:
    • xlThick
    • xlThin
    • xlHairline
    • xlMedium
  3. ColorIndex: refers to current color palette of the border derive from XlColorIndex enum which offers following constants:
    • xlColorIndexAutomatic
    • xlColorIndexNone
  4. Color: refers to actual border color that will apply around the cell it can have any valid VBA color constant or value with RGB
  5. ThemeColor: refers to color theme template which can be set to this parameter

Code example

Public Sub BorderAroundToRange()
    'Declare Range object
    Dim oRange As Range
    
    'Bind current selection to range object
    Set oRange = Selection
    
    'apply border around each cell in selection
    Dim oCell As Range
    For Each oCell In oRange.Cells
        'Apply border
        oCell.BorderAround LineStyle:=XlLineStyle.xlDash, Weight:=xlThick, ColorIndex:=xlColorIndexAutomatic, Color:=vbRed
    Next oCell
    
    'Memory cleanup
    Set oRange = Nothing
End Sub

Output

Please leave your comments or queries under comment section also please do subscribe to our blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.