MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Marco for borders


Posted by Paul on September 20, 2001 6:37 PM

I need a Marco that will take the active cell and 2 cells to the right and 3 rows down,12 cells in all, and put a "medium" inside and outside border on them. I am using Excel '97. Thanks for your help


Posted by Tom Urtis on September 20, 2001 8:39 PM

Here's your code Paul

Sub Borders()

Application.ScreenUpdating = False

Range(ActiveCell, ActiveCell.Offset(3, 2)).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
ActiveCell.Select

Application.ScreenUpdating = True

End Sub

Hope this is what you're looking for, if not let me know. You probably already knew how to produce the border part of the code thru the macro recorder which I reproduced here in its unabbreviated state. The only part you were probably stuck on was the range selection line which needs to be entered directly when dealing with active cells or dynamic ranges as with your situation.

Tom Urtis

Posted by Paul on September 21, 2001 5:20 AM

Thanks Tom, that was where I was stuck

Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With

: I need a Marco that will take the active cell and 2 cells to the right and 3 rows down,12 cells in all, and put a "medium" inside and outside border on them. I am using Excel '97. Thanks for your help