Apply portions of Auto Format?

[Ed Sheehan]

I have a table consisting of 150 pages of data with subtotal rows in a
reverse color band. I would like to apply an alternating
gray-band/white-band to every three rows for easy sighting across the page.
Am I stuck with selecting three rows, applying the gray fill, selecting
three more rows (skipping three for white), applying the gray fill, and
repeating 75 times?

Or is there a way to apply these bands more efficiently? If a macro is the
only way, it needs to skip any rows which have color already applied. These
are the subtotal rows.

It doesn't need to stay a table if that helps.

Thanks,

Ed

 

[Lene Fredborg]

The macro below should do what you describe. The selection must be somewhere
in the table when you start the macro.

Note that you need to change the color defined as wdColorLightOrange in the
macro - must be the color you want to keep (your reverse color). To find out
which color that is in VBA, you can record a macro while setting the color -
then locate the BackgroundPatternColor in the macro.

If you add or remove rows in the table, you can simply run the macro again
to reapply colors in 3-row bands.


Sub ChangeTableColor()

Dim oTable As Table
Dim n As Long
Dim i As Long
Dim oRange As Range
Dim oColor As WdColor

Set oTable = Selection.Tables(1)

For n = 1 To oTable.Rows.Count Step 3
'Keep orange shading, else
'Add gray every second time, else remove shading
If n Mod 2 = 0 Then
oColor = wdColorGray125
Else
oColor = wdColorAutomatic
End If

'To keep orange, needed to check rows individually
For i = 0 To 2
If n + i > oTable.Rows.Count Then Exit For
Set oRange = oTable.Rows(n + i).Range
'Change to 3 rows
With oRange
'Next line: Change wdColorLightOrange to the color or you
subtotals
If .Cells.Shading.BackgroundPatternColor <>
wdColorLightOrange Then
.Cells.Shading.BackgroundPatternColor = oColor
End If
End With
Next i
Next n

Set oTable = Nothing
Set oRange = Nothing
MsgBox "Finished applying table color."
End Sub

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word