Solved: Conditional formating using mailmerge content as a condition

[Lenart]

Hi!

I need help on MS Word Macro in VBA. I spend many hours per every year painting stupid tables representing refrigerators in MS Word. I have tried to program a VBA Macro to do it instead of me, but due to my poor programing skills, so far with no success.

It is not easy to explain what I need, that's why I have prepared simplified MS Word file for better explanation.

Basically: I prepare all data in MS Access, than I transfer it to the MS Word file(s) using mailmerge. So far so good. But I need some conditional formatting in MS Word file after that, and moreover: I need to use mailmerge field as condition. And that is the core of my problem.

My idea is, to do it with MS Word Macro, but it is also possible to prepare some calculations in MS Access before, if needed. Please see attached MS Word file. It's everything explained there. On the first page is the START POINT, and on the second page the RESULT.

I am using Office 2010.
I really appreciate your effort on my problem. Thanks in advance!

[macropod]

It doesn't seem to me that you need a macro for the mailmerge part, or to access a mergefield. Rather, all it appears you need is a macro to read the table cell values and colour the cells accordingly - after the mailmerge has been run. That said, it isn't clear to me what the conditions are that determine the colouration. It appears to be based on deviation from the average temperature, but it's not even apparent whether that is to be assumed from {MERGEFIELD 04} or calculated.

[Lenart]

You are right. If I think about it once again, the condition must be generated in "finished" MS Word file without mailmerge fields, because the MS Word file is usually generated for up to 60 refrigerators and every refrigerator has it's own coloration.

Be aware that this was simple example. Field {MERGEFIELD 04} is not always the average. Furthermore, it is even not necessary that any field in refrigerator table is equal to average. But this should not be a problem. The average can be picked up elsewhere in the MS Word file (example: field in lower right corner{ MERGEFIELD Average} ) or it can be calculated.

The rules for colors are in color legend. (Example: if deviation from average is less or equal than +/-0,5oC, the color is white, etc.). Color legend is valid for the table in lower left corner. The other two tables just need a "color copy".

[macropod]

It's not clear from your document whether your tables have a header row. Assuming they don't, the following should work:
Sub Demo()
Dim Tbl As Table, i As Long, Rng As Range, SngTmps As Single, SngAvg As Single
For Each Tbl In ActiveDocument.Tables
For i = 1 To Tbl.Rows.Count
Set Rng = Tbl.Rows(i).Cells(2).Range
Rng.End = Rng.End - 3
SngTmps = SngTmps + Trim(Rng.Text)
Next
SngAvg = SngTmps / (i - 1)
For i = 1 To Tbl.Rows.Count
With Tbl.Rows(i).Cells(2)
Set Rng = .Range
With Rng
.End = .End - 3
SngTmps = Trim(.Text) - SngAvg
End With
If .Row.Cells(1).Shading.Texture = wdTextureNone Then
With .Row.Shading
Select Case SngTmps
Case Is > 3.5: .BackgroundPatternColor = 255
Case Is < -3.5: .BackgroundPatternColor = 838860
Case Is > 3#: .BackgroundPatternColor = 8420607
Case Is < -3#: .BackgroundPatternColor = 16711680
Case Is > 2.5: .BackgroundPatternColor = 39423
Case Is < -2.5: .BackgroundPatternColor = 16750899
Case Is > 2#: .BackgroundPatternColor = 52479
Case Is < -2#: .BackgroundPatternColor = 16764006
Case Is > 1.5: .BackgroundPatternColor = 65535
Case Is < -1.5: .BackgroundPatternColor = 16764057
Case Is > 1#: .BackgroundPatternColor = 10092543
Case Is < -1#: .BackgroundPatternColor = 16772300
Case Is > 0.5: .BackgroundPatternColor = 13434879
Case Is < -0.5: .BackgroundPatternColor = 16777164
Case Else: .BackgroundPatternColor = -16777216
End Select
End With
Else
With .Shading
Select Case SngTmps
Case Is > 3.5: .BackgroundPatternColor = 255
Case Is < -3.5: .BackgroundPatternColor = 838860
Case Is > 3#: .BackgroundPatternColor = 8420607
Case Is < -3#: .BackgroundPatternColor = 16711680
Case Is > 2.5: .BackgroundPatternColor = 39423
Case Is < -2.5: .BackgroundPatternColor = 16750899
Case Is > 2#: .BackgroundPatternColor = 52479
Case Is < -2#: .BackgroundPatternColor = 16764006
Case Is > 1.5: .BackgroundPatternColor = 65535
Case Is < -1.5: .BackgroundPatternColor = 16764057
Case Is > 1#: .BackgroundPatternColor = 10092543
Case Is < -1#: .BackgroundPatternColor = 16772300
Case Is > 0.5: .BackgroundPatternColor = 13434879
Case Is < -0.5: .BackgroundPatternColor = 16777164
Case Else: .BackgroundPatternColor = -16777216
End Select
End With
End If
End With
Next
Next
End Sub
The above assumes you're actually using table averages, but your examples suggest you might actually be using median values. If so, you'll need to replace:
For i = 1 To Tbl.Rows.Count
Set Rng = Tbl.Rows(i).Cells(2).Range
Rng.End = Rng.End - 3
SngTmps = SngTmps + Trim(Rng.Text)
Next
SngAvg = SngTmps / (i - 1)
with:
i = -Int(-Tbl.Rows.Count / 2)
Set Rng = Tbl.Rows(i).Cells(2).Range
Rng.End = Rng.End - 3
SngAvg = Trim(Rng.Text)

[Lenart]

Hi!

It looks great. Thanks!
But with my Word file it stops at the beginning and I don't know why.

It stops at:
Set Rng=Tbl.Rows(i).Cells(2).Range

With message:
Run-time error '5941'
"The requested member of the collection does not exist."

It looks that macro has a some problem with my table(s).

If I run Macro on file without tables: no error, no effect.
If I create a new Word file, put one simple table in, and I run a Macro, I get Error 13; Type mismatch; two rows further than before.

[macropod]

It would help if you could attach a document with samples of the tables you're actually using.

[Lenart]

Typical file is 2,5MB long (without linked files) and as far I know, too long for this forum. "@gmail.com" might help. "royce67i"

[macropod]

As I said: samples.

Also, as I said in post #4, the code assumed your actual tables don't have header rows. The error you described suggests otherwise. In that case, change:
For i = 2 To Tbl.Rows.Count
to:
For i = 2 To Tbl.Rows.Count
(twice) and change:
SngAvg = SngTmps / (i - 1)
to:
SngAvg = SngTmps / (i - 2)

[Lenart]

Thank you Paul!

Sorry for late reply. I needed some time to "digest" your code.
With some minor changes I was able to solve the problem.

I couldn't address the tables like:
"For Each Tbl In ActiveDocument.Tables"
It didn't work in my file. But when I address them separately, like Table 1, Table 2, it was OK.
Thanks again!

PROBLEM SOLVED

[macropod]

In that case, please mark the thread as solved, which you do via 'Tread Tools'.