Solved: help

[abraham30]

Dear friend,
In the excel sheet, I want to border all row which are in dynamic range both in row and column side, autofit column width, keep filter in row no 1 and make that row in bold fant and color as white, background darker.

I did everything. Can anybody rectify the error. It is unable to select dynamic range although I have mention

[Bob Phillips]

Sub Macro1()
Dim rng As Range
Dim lLastRow As Long, i As Long

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:E1").Resize(lLastRow)

rng.Borders(xlDiagonalDown).LineStyle = xlNone
rng.Borders(xlDiagonalUp).LineStyle = xlNone
Call SetBorder(rng, xlEdgeLeft)
Call SetBorder(rng, xlEdgeTop)
Call SetBorder(rng, xlEdgeBottom)
Call SetBorder(rng, xlEdgeRight)
Call SetBorder(rng, xlInsideVertical)
Call SetBorder(rng, xlInsideHorizontal)

Cells.Columns.AutoFit
With Range("A1:E1")

.AutoFilter

With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With

.Font.Bold = True
End With
End Sub

Private Function SetBorder(rng As Range, border As XlBordersIndex)
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Function

[abraham30]

Thanks Xld.
The macro work for column in dynamic range. Is it possible to keep the same for dynamic row also.
Advance thanks

[Bob Phillips]

Can you clarify what you mean by that?

[abraham30]

Dear Xld,
Suppose few new columns are added. Then the macro can not bordered that output (It is limit upto column E, With Range("A1:E1")).
Is it possible to border all column which are dynamically added like row

[Bob Phillips]

Sub Macro1()
Dim rng As Range
Dim lLastRow As Long
Dim lLastCol As Long
Dim i As Long

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
lLastRow = Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = Range("A1").Resize(lLastRow, lLastCol)

rng.Borders(xlDiagonalDown).LineStyle = xlNone
rng.Borders(xlDiagonalUp).LineStyle = xlNone
Call SetBorder(rng, xlEdgeLeft)
Call SetBorder(rng, xlEdgeTop)
Call SetBorder(rng, xlEdgeBottom)
Call SetBorder(rng, xlEdgeRight)
Call SetBorder(rng, xlInsideVertical)
Call SetBorder(rng, xlInsideHorizontal)

Cells.Columns.AutoFit
With Range("A1").Resize(, lLastCol)

.AutoFilter

With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With

.Font.Bold = True
End With
End Sub

Private Function SetBorder(rng As Range, border As XlBordersIndex)
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Function