Need help with vba code to create a table border.

[Lidsavr]

I am writing code with Excel 2007 (Excel 2003 users sometimes run the code).

I think what I have is pretty simple, but I do not understand how it works. (I am trying to learn write code so it is clean (i.e, no excess data)).

An example of what I am trying to do is formatting the cell borders on a table:

I have an older VBA book that I reference and it says "The Borders collection contains four elements identified by these constants: xlTop, xlBottom, xlRight, and xlLeft. You can set the properties for these elements individually or all at once (J.Webb, 1996, p.358)." The book does not give an example of all at once and the macro recorder lists them individually (example):

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -8369125
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -8369125
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -8369125
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Color = -8369125
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Color = -8369125
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A7:E7").Select
    Range("E7").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8408091
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

I've tried rewriting the code to make it shorter, but I get errors and the code will not execute (on the line in red):

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
[COLOR="Red"]    With Selection.Borders(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)[/COLOR]        
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
    End With

How can I set the properties for the four elements "all at once" (like the author states)?

Thanks,

Lidsavr

References:
J.Webb. (1996). Special Edition Using Excel Visual Basic for Applications: Second Edition. QUE Publishing. Indianapolis, Indiana

 

[VoG]

Try like this

    With Selection.Borders
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
    End With

 

[Lidsavr]

VoG,
One again, I thank you! I did not know I could remove the element and the cell borders would all format together.

I have learned much from you over the past year and am looking forward to learning more in the future...

Respectfully,

Lidsavr

 

[VoG]

You are welcome.

If you just want the defaults (and I don't think TintAndShade will work in Excel 2003)

    With Selection.Borders
        .Weight = xlThin
    End With