conditional format with mail merge (word 2000)

Topic

New Reply

Is it possible to mail merge into a catalog and create conditional formatting (cell color)?

Reply | Quote

Replies

Hi, Sam. A little better explanation of the conditional format would be great.

You want to change a table’s cell color. What’s the criteria for that? If it can’t be with a mail merge field statement, it might be easily done with some macro code.

Reply | Quote

Merging a collection of info into a catalog main document. Say if one field is over ten, the cell background in the catalog should be blue. If the next record has a negative number in that field it should be red.

Reply | Quote

Will this value be SHOWN in the document or just used as the criteria?

What is this field value?

Reply | Quote

Yes the value is one of the pieces of data to be shown. It could be a dollar value ie profit or loss for a transaction or change in price from previous. The desire is to create a catalog (report) in word that will call attention to transactions or items which are out of the ordinary.

Reply | Quote

Indeed, then this would require VBA code. There are ways to “mail merge” without mail merging using code. I cannot write it myself, tho I’ve just recently managed a couple of projects that perform similar tasks. See if you can get someone to write the code for you. Unfortunately (hee hee), I have to pay my programmers.

Reply | Quote

I don’t know how much Word functionality you need in the catalog.

For a relatively simple list, you might consider copying the merged table to Excel. In Excel, you can apply conditional formatting to cells without programming.

Reply | Quote

Here is an example that tests on a single condition. You’ll have to adapt it to work with multiple conditions.

Assumptions: the merged catalog is a Word table.
The test here is that the first cell in a row contains 1.

Sub ShadeAlternate()
Dim tbl As Table
Dim intRow As Integer
Dim strText As String
‘ Table object
Set tbl = ActiveDocument.Tables(1)
‘ Loop through rows, skip header row
For intRow = 2 To tbl.Rows.Count
‘ Get text from cell in first column
strText = tbl.Cell(intRow, 1).Range.Text
‘ Get rid of invisible characters at end of cell
strText = Left(strText, Len(strText) – 2)
‘ Test condition
If strText = “1” Then
‘ Set shading
With tbl.Rows(intRow).Shading
.Texture = wdTextureNone
.ForegroundPatternColorIndex = wdAuto
.BackgroundPatternColorIndex = wdYellow
Else
.Texture = wdTextureNone
.ForegroundPatternColorIndex = wdAuto
.BackgroundPatternColorIndex = wdBrightGreen
End With
End If
Next intRow
Set tbl = Nothing
End Sub

Reply | Quote