How create border in excel vba

[Mouli]

I am trying a border using VBA in excel but am getting object required error. Kindly help me to fix

[Paul_Hossler]

Without seeing your code and knowing what kind of borders you want, it's hard to say

The first is the macro recorder, the second is some cleanup

Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
    Range("A1:D12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    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
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub


Sub Macro2()
    With Range("A1:D12")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
End Sub

[Mouli]

Without seeing your code and knowing what kind of borders you want, it's hard to say

The first is the macro recorder, the second is some cleanup

Option Explicit

Sub Macro1()
    ' Macro1 Macro
    Range("A1:D12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    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
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Sub Macro2()
    With Range("A1:D12")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
End Sub

Sub ApplyDynamicBorder()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim tableRange As Range
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Find the last column with data in row 1
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Define the range of the table
    Set tableRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Apply borders to the table range
    With tableRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 0
    End With
End Sub

[Mouli]

I am using above code but am getting object required error and I am trying here to create dynamic border based on the data changes in source file

[p45cal]

I get no error with your ApplyDynamicBorder code!
It gives a thin black border around every cell in the table.
Is that what you want?
Paul's suggestion was to put a border around the whole range (table) and no borders around each cell. If that's your preference then your section ' Apply borders to the table range might be reduceable to one line:

tableRange.BorderAround 1

however, you might want a bit more control, eg. a medium thickness green continuous border:

tableRange.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, Color:=RGB(143, 219, 41)

More info:
https://learn.microsoft.com/en-us/of...e.borderaround

[Mouli]

Yes you are right. But am getting object required error while running that code. Could you please me anything I am missing over there

[Mouli]

It is worked now. Thanks for your help