VBA To Add Borders

[jo15765]

Greetings - this works, but is more of an optimization question. I am wanting to add borders (all) around my selected range. Is there a shorter way to write this code? This is what the Macro Recorder created

Range("A1:" & colName & lRow).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("A1").Select

 

[mikerickson]

something like

Selection.BorderAround LineStyle:=xlContinuous, Weight:=xlThin, Color:=RGB(255, 0, 0)

 

[jo15765]

@mikerickson -> that does the "outside" but not the inside.

 

[dmt32]

Hi,

try

Sub FormatBorders(ByVal Target As Range)
'
' Format Macro
'
'
    With Target
        .ClearFormats
'inside
        .Borders.ColorIndex = 3
        .Borders.Weight = xlThin
'outside
        .BorderAround Weight:=xlThick, ColorIndex:=3
    End With
End Sub

to call it

FormatBorders Range("A1:" & colname & lRow)

Dave

 

[jo15765]

@Dave -> this only formats row 1 - not the used range....

Disregard, I had to qualify one of my variables with .ws -- this works as desired.. Thanks!

 

[dmt32]

@Dave -> this only formats row 1 - not the used range....

Disregard, I had to qualify one of my variables with .ws -- this works as desired.. Thanks!

Sub will format the range you pass to it

example

FormatBorders Range("D10:H20")

should format the specified range

Example I first posted was based on your original code & assumes that your variables hold the required range values.
If you are doing something different then post it here.

Dave