controlling cell borders in excel using VB

[eli silverman]

I hve a VB application that is using the copyfromrecordset
function to transfer the an ADODB recordset to an excel
file starting in cell A8 filling the range ("A8":"Z37")
"Z37" varies depending on the query being executed. The
resulting range could be anywhere from ("A8":"D9") to
("A8":"BZ1200")
I would like to set the border to "all borders" just for
the specified range. Can anyone explain how this can be
acomplished?
Any help would be greatly appreciated

 

[TroyW]

Eli,

Here is one thought. How about using "Conditional Formatting".

1) Select cell A8 on your worksheet.
2) Select "Format | Conditional Formatting..." from the menubar.
3) In the dropdown box select "Formula Is".
4) Type: =LEN(A8)>0 in the edit box to the right
5) Click the Format... button.
6) Select the Border tab and click the "Outline" button and click OK.
7) Click OK in the original dialog box.
8) Copy cell A8 and Paste Special (Formats ONLY) to cells A8:BZ1200

If there is any number or text in any of the cells in the range A8:BZ1200,
they will show the outline format. If the cell results in an error (i.e.
#DIV0!, #VALUE, etc.) it won't be given the formatting. If you want error
cells to also be formatted then add a second conditional formatting by
clicking the "Add>>" button and using a formula of: =ISERR(A8)

Troy

 

[Tom Ogilvy]

Sub AddBorders()
With Range("A8").CurrentRegion
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub