in Excel, how to put gridline every ten rows

[Guest]

How can I set up the procedure (formatting, macro, whatever) to place a
gridline every ten rows on my spreadsheets? Or, how can I get it to put a
gridline at every TOTAL line, when I apply subtotals? I have EXCEL 97.

 

[Don Guillett]

try this?
Sub insertline()
For Each c In Range("a2:a100")
If UCase(c) = "TOTAL" Then
With c.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
Next c
End Sub

 

[Guest]

I could not get what you sent to work, but that is probalby me rather than
Excel. So I asked a couple others at work to look at it. After a few
versions based on what you gave me, this is what we came up with to put a
line every ten rows.

Sub insertline()
Dim x As Long
For Each c In Rows
x = x + 1
If x Mod 10 = 0 Then
With c.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
Next c
End Sub

Thank you so much.

 

[Don Guillett]

What I sent was tested for any cell in col A that said Total or TOTAL or
total.
What you sent will put a line every 10 rows to the bottom of the workbook
causing BLOAT. BAD!!

 

[Guest]

Yes it did exactly that. But I can deal with that, some of my spreadsheets
are over 35000 rows, so it is ok. And it does put the gridline across the
entire line, rather than in just one column. So all is well. Again thank
you for your help.

 

[Don Guillett]

glad you are happy

 

[Debra Dalgleish]

You could add the gridlines with conditional formatting:

Turn off the gridlines (Tools>Options, View tab)
Select all the cells on the worksheet
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =MOD(ROW(),10)=0
Click the Format button

 

[Don Guillett]

Debra, As usual, a great solution without creating file bloat.

 

[Debra Dalgleish]

Thanks Don!

Debra, As usual, a great solution without creating file bloat.