Formatting cell borders thru VBA

[PATSYS]

Hi all,

I have recorded a macro to create some borders on a certain range, I have then somehow managed to trim the code and ended up with below:

    With Range("B2:G2")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
        
    With Range("B2:B16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
        
    With Range("C2:C16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
    
    With Range("D2:D16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
    
    With Range("E2:E16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
    
    With Range("F2:F16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With

    With Range("G2:G16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With

Question: Can above be even made shorter, but achieve the same result?

Sorry I do not post how to post my sheet here to show the borders.., so pls run the above code to see the format that I am referring to.

Thanks

 

[Norie]

How about this?

    Range("B2:G2").Borders(xlEdgeBottom).LineStyle = xlContinuous
    With Range("B2:G16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

 

[Seti]

Try this and see if it is what you are looking for:

    With Range("B2:G2")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
        
    With Range("B2:G16")
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

Looks like Norie was quicker on the draw.

 

[MickG]

Or:-

[COLOR="Navy"]Sub[/COLOR] MG02Sep32
[COLOR="Navy"]Dim[/COLOR] col
[COLOR="Navy"]For[/COLOR] col = 1 To 6
Range("A2:A16").Offset(, col).BorderAround Weight:=xlThin
[COLOR="Navy"]Next[/COLOR] col
Range("B2:G2").BorderAround Weight:=xlThin
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Norie]

Dang, keep on forgetting about BorderAround.

    Range("B2:G2").Borders(xlEdgeBottom).LineStyle = xlContinuous
    With Range("B2:G16")
        .BorderAround Weight:=xlThin
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With

 

[PATSYS]

Hi Norie, MickG and Seti,

Thanks for your posts, I will probably go with Norie's suggestion.

Thanks again.