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/office/vba/api/excel.range.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