Formatting Without Selecting Range

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

Hello Dennis,

This macro will format your ranges without selecting them. It places a box around the cells, but this is to give you an idea how to do this. Change the worksheet name in blue to the worksheet your reformatting.

Sub ReFormatSheet()

  Dim FormatRng As Range
  Dim I As Long
  Dim Ranges As Variant
  Dim Rng As Range
  
    With Worksheets("Sheet1")
      Ranges = Split("A2:M15,A16:M29,A30:M43,A44:M57,A58:M71,A72:M85,A86:M99," _
                      & "A100:M113,A114:M127,A128:M141,A142:M155,A156:M169", ",")
      Set FormatRng = Union(.Range(Ranges(0)), .Range(Ranges(0)))
        For I = 0 To UBound(Ranges)
          Set FormatRng = Union(FormatRng, .Range(Ranges(I)))
        Next I
    End With
    
    For Each Rng In FormatRng.Areas
      With Rng
        For I = 7 To 10
          With .Borders(I)
            .Weight = xlMedium
            .LineStyle = xlContinuous
          End With
        Next I
      End With
    Next Rng
    
End Sub

Sincerely,
Leith Ross

Good afternoon Leith!

Thank you for your help!

I played with your code to adapt to what my associate wanted to happen. Here is how I ended up:

Sub FormatBorders()
Dim FormatRng As String, I As Long, Rng As Range, myColor As Long
    Application.ScreenUpdating = False
    myColor = 1
    FormatRng = "A1:M1"
    Set Rng = Range(FormatRng)
    With Rng
        For I = 7 To 11
            With .Borders(I)
                .Weight = xlThin
                .LineStyle = xlContinuous
                .ColorIndex = myColor
            End With
        Next I
    End With
    
    FormatRng = "A2:M15,A16:M29,A30:M43,A44:M57,A58:M71,A72:M85," & _
        "A86:M99,A100:M113,A114:M127,A128:M141,A142:M155,A156:M169"
    Set Rng = Range(FormatRng)
    With Rng
        For I = 7 To 10
            With .Borders(I)
                .Weight = xlThin
                .LineStyle = xlContinuous
                .ColorIndex = myColor
            End With
        Next I
        For I = 11 To 12
            With .Borders(I)
                If I = 12 Then myColor = 15
                .Weight = xlThin
                .LineStyle = xlContinuous
                .ColorIndex = myColor
            End With
        Next I
    End With
    Application.ScreenUpdating = True
End Sub