PDA

View Full Version : [SOLVED] Borders on a Range of Cells



qcoleman
05-23-2012, 07:41 AM
I would like to know if there is a better way to put “All borders” around a range of cells other than the multi line code that I have.

Sub Macro3()
' Macro3
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
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
End Sub

Bob Phillips
05-23-2012, 09:42 AM
Not better particularly, but neater


Sub Macro3()
Dim rngBorders As Range
Set rngBorders = Selection
Call SingleBorder(rngBorders, xlEdgeLeft)
Call SingleBorder(rngBorders, xlEdgeTop)
Call SingleBorder(rngBorders, xlEdgeBottom)
Call SingleBorder(rngBorders, xlEdgeRight)
Call SingleBorder(rngBorders, xlInsideVertical)
Call SingleBorder(rngBorders, xlInsideHorizontal)
End Sub

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

qcoleman
05-23-2012, 12:06 PM
Thanks That work perfect!:thumb