I have an associate who has a "template" he uses for scheduling our production. He frequently highlights and drags items in a schedule from one area (time frame) to another.
When he does this he end up with the default border formatting in the area he dragged from.
I created a simple macro for him that will reformat his work range to the original settings.
Application.ScreenUpdating = False
Range("A2:M15,A16:M29,A30:M43,A44:M57,A58:M71,A72:M85,A86:M99,A100:M113,A114:M127,A128:M141,A142:M155,A156:M169").Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
There are a couple of specific ranges with similar, but different border formats.
My question is: How do I accomplish the formatting of borders without actually selecting the range.
He would like to have a button that will do the formatting, but not end up with the active cell changing when he is done.
This worked for me:
Sub FormatBorders()
Dim myRange As String
Application.ScreenUpdating = False
myRange = "A1:M1"
With Range(myRange).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
myRange = "A2:M15,A16:M29,A30:M43,A44:M57,A58:M71,A72:M85," & _
"A86:M99,A100:M113,A114:M127,A128:M141,A142:M155,A156:M169"
With Range(myRange).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range(myRange).Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
Application.ScreenUpdating = True
End Sub
Bookmarks