Row Formatting Using VBA

[eded1218]

I am a new VBA user. I would like to format the rows in a Word table
based on the contents of the first cell in the row (column #1), which
will be either "a", "p", or "d" (which represents active, pending or
declined) or the cell could be blank.

So, for example, if the first cell is blank, I don't want to make any
changes to the row's format. However, if the first cell contains "a",
I would like the font of the entire row to be white, the fill to be
green and the font to be bold. And so on, with different colors/
formatting for each row that contains a different letter.

This doesn't seem like it should be that difficult, but I haven't
really found that much information on how to work with tables using
VBA.

I would really appreciate any help!

Also, could anyone recommend a good book on using VBA in Word? Thanks
in advance!

 

[Jay Freedman]

This macro could be modified to make it more efficient, but I left
everything spelled out so you can follow it more easily.

Sub demo()
Dim myTbl As Table
Dim myRow As Row

' this assumes there is at least one table
' and the first is the one you're formatting
Set myTbl = ActiveDocument.Tables(1)

' go through all the rows
For Each myRow In myTbl.Rows
If myRow.Cells(1).Range.Characters(1).Text = "a" Then
myRow.Range.Font.Bold = True
myRow.Range.Font.Color = wdColorWhite
myRow.Shading.BackgroundPatternColor = wdColorGreen
End If

If myRow.Cells(1).Range.Characters(1).Text = "p" Then
myRow.Range.Font.Bold = False
myRow.Range.Font.Color = wdColorYellow
myRow.Shading.BackgroundPatternColor = wdColorRed
End If

If myRow.Cells(1).Range.Characters(1).Text = "d" Then
myRow.Range.Font.Bold = True
myRow.Range.Font.Color = wdColorGray10
myRow.Shading.BackgroundPatternColor = wdColorBlue
End If
Next
End Sub

There are a couple of ways to handle tables in VBA. The first job is
to locate the particular table you want to work on (or else use a For
Each to work on all the tables in the document). That's the effect of
the Set statement.

The table object has several collections that let you work with its
pieces in various ways. This demo shows working with the Rows
collection, by using a For Each statement to cycle through all the
rows. There is also a Columns collection, which would be useful if you
wanted to format all the cells in a column the same way. And there's a
Cells collection, from which you can pick out individual cells by row
and column coordinates.

Within each row, the code checks the text in the first cell in that
row's Cells collection (not to be confused with the table's Cells
collection). Depending on what is there, at most one of the If
statements will have a true condition; if the first cell is blank or
has some other letter in it, then none of the If statements will have
a true condition.

The multiple If statements could be collected into a Select Case
statement for better efficiency.

 

[eded1218]

This macro could be modified to make it more efficient, but I left
everything spelled out so you can follow it more easily.

Sub demo()
Dim myTbl As Table
Dim myRow As Row

' this assumes there is at least one table
' and the first is the one you're formatting
Set myTbl = ActiveDocument.Tables(1)

' go through all the rows
For Each myRow In myTbl.Rows
If myRow.Cells(1).Range.Characters(1).Text = "a" Then
myRow.Range.Font.Bold = True
myRow.Range.Font.Color = wdColorWhite
myRow.Shading.BackgroundPatternColor = wdColorGreen
End If

If myRow.Cells(1).Range.Characters(1).Text = "p" Then
myRow.Range.Font.Bold = False
myRow.Range.Font.Color = wdColorYellow
myRow.Shading.BackgroundPatternColor = wdColorRed
End If

If myRow.Cells(1).Range.Characters(1).Text = "d" Then
myRow.Range.Font.Bold = True
myRow.Range.Font.Color = wdColorGray10
myRow.Shading.BackgroundPatternColor = wdColorBlue
End If
Next
End Sub

There are a couple of ways to handle tables in VBA. The first job is
to locate the particular table you want to work on (or else use a For
Each to work on all the tables in the document). That's the effect of
the Set statement.

The table object has several collections that let you work with its
pieces in various ways. This demo shows working with the Rows
collection, by using a For Each statement to cycle through all the
rows. There is also a Columns collection, which would be useful if you
wanted to format all the cells in a column the same way. And there's a
Cells collection, from which you can pick out individual cells by row
and column coordinates.

Within each row, the code checks the text in the first cell in that
row's Cells collection (not to be confused with the table's Cells
collection). Depending on what is there, at most one of the If
statements will have a true condition; if the first cell is blank or
has some other letter in it, then none of the If statements will have
a true condition.

The multiple If statements could be collected into a Select Case
statement for better efficiency.











--
Regards,
Jay Freedman
Microsoft Word MVP FAQ:http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.- Hide quoted text -

- Show quoted text -

Thank-you! That's exactly what I needed and it worked perfectly!