The following VBA procedure can be used to apply borders to a range of cells
Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)
On Error Resume Next
With ToRange
With .Borders(xlDiagonalDown)
.LineStyle = xlNone
End With
With .Borders(xlDiagonalUp)
.LineStyle = xlNone
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
End With
End Sub
Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)
On Error Resume Next
With ToRange
With .Borders(xlDiagonalDown)
.LineStyle = xlNone
End With
With .Borders(xlDiagonalUp)
.LineStyle = xlNone
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = intColorIndex
End With
End With
End Sub
Thanks! Im kinda looking into VBA right now, but i am not quite sure how much time it would save me. In this example, how many boders/worksheets would it take so its worth using VBA instead of normal excel functions? Usually i use guides like http://www.excel-aid.com/excel-borders-drawing-borders-around-cells-and-cell-ranges.html to reproduce the steps, in this case it seems fairly easy to do. So what would you recommend for someone who mostly edits only a few sheets every day, most of them "unique"?
ReplyDeleteHi Markus,
ReplyDeleteThanks for your comment, you normally wouldnt bother doing this using VBA unless you have a lot of worksheets to be formatted, however if you are generating some sheets through VBA code you might want the code to do the formatting too.
Thanks for the quick answer, i guess i will just keep doing it manually then. Untill i have a ton of sheets at least. So i hopefully will be back for some VBA lessons one day :)
ReplyDeletehehe most wekcome, there is no rule of thumb or threshold for when you should automate, its more a gut feeling and a consideration about will the automated solution be reused multiple times (maybe with some small tweaks). For starting of with VBA i suggest go through some web search on the basics. Here is one good link to start with http://dailydoseofexcel.com/archives/2004/04/28/beginning-vba-navigating-the-object-model/. If you are stuck and need help on any issue i will be glad to help
ReplyDelete