VBA formating borders (Excel 2000)

Topic

New Reply

I am having a problem trying to create borders over a range of cells in vba. The code works correctly when I do it on one cell, but doesn’t work when placed inside of a loop.
Dim r As Integer
Dim c As Integer

With Worksheets(“Table 3”)

random stuff…

For r = 7 To Cell_Count – 1
For c = 1 To c = 20
With .Cells(r, c).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Next c
Next r

I am pretty confident “Cell_Count-1” is not the problem because it works successfully with other operations in the event.

Reply | Quote

Replies

try changing the line “For c = 1 To c = 20” to “For c = 1 To 20” – any better?

Reply | Quote

Changing that made it a lot better. I can’t belief I made that mistake. Thanks for the help

Reply | Quote

How is “Cell_Count” dimensioned and typed? And

For c = 1 To c = 20

Is not valid syntax.

Usually the code would be something like (untested air code, and notice the leading dots):

With myRange
For r = 7 To (.Rows.Count – 1)
For r = 7 To (.Columns.Count)
With .Cells(r,c).Borders
… etc

Afterthought – the code is going to error out (or maybe just do nothing) for any range where the row or column count is < 7.

Reply | Quote

The following works for me, as long as I set Cell_Count greater than 8.


For r = 7 To Cell_Count - 1
For c = 1 To 20
With ActiveSheet.Cells(r, c).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next c
Next r


Reply | Quote

Perhaps a loop is not required.

Would

    With Range(Cells(7, 1), Cells(Cell_Count - 1, 20)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

do the same job ?

Andrew C

Reply | Quote