Mail Merge - Conditional formatting of cell background color

[jrouse]

I'm doing a mail merge of a list of inventory numbers with 3 mergefields to make a sheet of scan labels.

The cells are formatted as:

22572

I'm wanting to change the background color of the cell based on the CLASS mergefield. If the value is 08 I want the background to be colored yellow, otherwise I want the cell to be the default color.

What would be the VBA code for a macro to be able to do this?

Attached is my sample data.

Thanks

[macropod]

See Conditionally Shade Table Cells in the Mailmerge Tips and Tricks thread at:
http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

[jrouse]

Unfortunately using merge field conditions as shown in that thread doesn't give me the results I'm looking for.

I tried using the following code:
22576

It sort of works, but the background color doesn't fill the whole cell, and I lose all formatting.
22577

The following picture is how I want to cells to look. I assume i need to use VBA code to get this to look the way I want, but I'm not sure how to write it to select based on the mergefield. I'm using Word 2007 if that makes a difference.
22578

[macropod]

If you follow the instructions in the link, you'll get the entire cell shaded...

[far2many]

Hi,

I have followed a lot of threads about this on many sites but I am sorry that I am unable to replicate the successful outcome. Can anyone help me please. I am making a badge that has peoples names and score band on. HIGH should be green, MID orange and LOW RED. I would prefer not to have the words (HIGH, MID and LOW) on the badge. I just need the CELL CONDITIONALLY FORMATTED to the correct colour based on the score. For now I have a database with two fields - Name and Score. The below images are where I am up to with the outcome and code and the way I would like it to be. I need the whole cell filled and preferably not to have the word High in there. Where I am confused is on the mailmerge tips and tricks website it says to create a "Set of fields for each condition". I dont understand why? Especially when the example only uses the mergefield CONDITION and not CONDITION2 or CONDITION3 for each different condition - surely it means set up a single field that has different possibilities that can be picked up by the condition evaluation or comparator? If this is the case do I need to create that field could I not just use an existing field such as my Score field which has 3 possible outcomes (High, Mid, Low)? Another issue I am unable to solve is the TABS. It says use:

{IF{MERGEFIELD Condition}= "1" "→→↵
→Condition 1 Text→↵
→→" \* Charformat}

Where → is an actual TAB. When I press TAB when writing the formula it jumps out of the formula and takes me to the next cell along. Please can a GURU take me under the wing and help me on this? Thank you for reading.

23182
23183
23184

[macropod]

I just need the CELL CONDITIONALLY FORMATTED to the correct colour based on the score. For now I have a database with two fields - Name and Score. The below images are where I am up to with the outcome and code and the way I would like it to be. I need the whole cell filled and preferably not to have the word High in there.
You don't need to have any conditional text, just the required formatting. Note that you need to apply highlighting to the I of IF, not a font colour.

Where I am confused is on the mailmerge tips and tricks website it says to create a "Set of fields for each condition". I dont understand why?
Because you have 3 conditions and to need an IF test for each. That doesn't imply having a Condition1 field, a Condition2 field, and a Condition3 field; the same field can be tested for all 3 conditions. Hence:

{IF{MERGEFIELD Condition}= "HIGH" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "MID" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "LOW" "→→↵
→→↵
→→" \* Charformat}

[far2many]

Thanks for the advice I managed to get it working from here. The only issue I had left was the way to insert the TAB and Return Character. I copied them from your post and it worked a treat.

Thanks again - looks great!

[macropod]

The characters in my post were not actual tab & return characters, so I can't see how you could have used them and gotten anything that works correctly. As noted in the link, you need to replace those with actual tab & return characters, plus use appropriately-positioned tab-stops.

[AbbyPi]

Hi, I am trying to do the same thing - conditionally shade a table cell in a word mail merge table. I've used the above formatting, but only one word is highlighted, not the whole cell. Any suggestions? I set the table cell margins to 0 but am not sure what the second bullet point means. I cleared the tab stops and set them to right alignment, but this doesn't seem to be working. Is it because there are other merge fields in the same table cell?


24080

[macropod]

Your attachment link is invalid. You can attach files via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

[AbbyPi]

Your attachment link is invalid. You can attach files via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Here is the attachment.
24087

[macropod]

All your attachment is is an image. There is no way anyone can assess what you've done from that - we'd need the actual mailmerge main document and a description of what is to be formatted. As it is, it's also clear your cells contain text that isn't included within the field code so, whatever else happens, that text isn't going to be highlighted by the field coding.

[AbbyPi]

All your attachment is is an image. As it is, it's also clear your cells contain text that isn't included within the field code so, whatever else happens, that text isn't going to be highlighted by the field coding.

Thanks, that is what I thought. Is there a way to conditionally format the entire cell background?

[macropod]

Yes, and the instructions in post 6 show how to do that.

[AbbyPi]

Yes, and the instructions in post 6 show how to do that.

If I am understanding correctly, those instructions don't change the background color for the entire cell, just the text background of the specified field, so that if there are other merge fields or text in the cell, it will only highlight the specified field and not the whole table cell.

I was able to conditionally shade the entire cell by running this VBA macro after the mail merge.


Sub color()
Dim r As Range, text As String, backgroundColor As WdColorIndex
Set r = ActiveDocument.Range
text = "TOXIN"
backgroundColor = wdGray25
With r.Find
Do While .Execute(FindText:=text, MatchWholeWord:=True, Forward:=True) = True
r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor
Loop
End With
End Sub

[macropod]

If I am understanding correctly, those instructions don't change the background color for the entire cell, just the text background of the specified field, so that if there are other merge fields or text in the cell, it will only highlight the specified field and not the whole table cell.
You really do need to pay closer attention to the instructions...

[AbbyPi]

All your attachment is is an image. There is no way anyone can assess what you've done from that - we'd need the actual mailmerge main document and a description of what is to be formatted. As it is, it's also clear your cells contain text that isn't included within the field code so, whatever else happens, that text isn't going to be highlighted by the field coding.

Please let me know what I am missing. It is not helpful to just say "pay attention to the instructions" as I followed them exactly (or so I thought). I realize I am not an expert at Word but have spent several hours trying to get this to work.

[macropod]

Quite clearly, you have content outside the field construction - which I drew your attention to in post 12 and you conceded in post 13 - whereas the example clearly shows all the content within it. Moreover, I explicitly told you in post 12 why your approach won't work.

[gmaxey]

AbbyPi.

It does work.

To get the concept just forget about merge fields for a minute.

1. Create a 1 row x 2 column table.
2. Set the cell margins right, left, top and bottom to 0 pts.
3. Create a simple conditional IF field as shown.
4. Format the "I" in "IF" with yellow highlight
5. Insert a right aligned tab space just passed the right border of cell 1.

24105

6. Select all, update fields and toggle field codes.
7. Since the condition is met, the entire content of the cell will be highlighted yellow.
8. Toggle fields codes, change 1 = 1 to 1 = 2. Repeat select, update toggle and the highlighting goes away.

[AbbyPi]

I agree that this works and I had no problem getting it to work with only one field in the cell.

However, because it uses text highlighting and not cell background shading, it does not work with additional text outside the field code, which was pointed out and which is what I was hoping to solve by asking for help in this forum. I apologize if my question was not clear.

[macropod]

The answer is simple: repeat all of the static text inside each field.

[AbbyPi]

The answer is simple: repeat all of the static text inside each field.

The additional text isn't static, it's other mail merge fields.

[macropod]

The additional text isn't static, it's other mail merge fields.
So replicate those mergefields, too. This isn't rocket science... And, as I said in post 12:


All your attachment is is an image. There is no way anyone can assess what you've done from that

[wrd_xcl]

Hi All,

I followed the instructions above and use this as a reference:

{IF{MERGEFIELD Condition}= "HIGH" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "MID" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "LOW" "→→↵
→→↵
→→" \* Charformat}

I've set the margins (all sides) to 0, the tab is set to cell width, it's right-aligned, I use TAB (ctrl-TAB) because it's inside the cell, and for RETURN (shift-enter).but all I got is "Unknown Switch Argument". Please help

Here's a screenshot of current work. I highlighted them to show the tabs.

27041

[Nikko77]

"This worked like a dream! I am very rusty with VBA though, can someone please tell me how to ask it to perform multiple actions. I forget which part of the code I need to copy for it to repeat.

I would like it to find the following and shade the cell that colour:

text = "W4"
backgroundColor = wdRed


text = "W5"
backgroundColor = wdRed


text = "W6"
backgroundColor = wdYellow


text = "W7"
backgroundColor = wdYellow


text = "W8"
backgroundColor = wdGreen


text = "W9"
backgroundColor = wdGreen


Thanks



If I am understanding correctly, those instructions don't change the background color for the entire cell, just the text background of the specified field, so that if there are other merge fields or text in the cell, it will only highlight the specified field and not the whole table cell.

I was able to conditionally shade the entire cell by running this VBA macro after the mail merge.

Sub color()
Dim r As Range, text As String, backgroundColor As WdColorIndex
Set r = ActiveDocument.Range
text = "TOXIN"
backgroundColor = wdGray25
With r.Find
Do While .Execute(FindText:=text, MatchWholeWord:=True, Forward:=True) = True
r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor
Loop
End With
End Sub

[Chas Kenyon]

I agree that this works and I had no problem getting it to work with only one field in the cell.

However, because it uses text highlighting and not cell background shading, it does not work with additional text outside the field code, which was pointed out and which is what I was hoping to solve by asking for help in this forum. I apologize if my question was not clear.
Can you put your additional text inside the field code?

[macropod]

"This worked like a dream! I am very rusty with VBA though, can someone please tell me how to ask it to perform multiple actions. I forget which part of the code I need to copy for it to repeat.

I would like it to find the following and shade the cell that colour:
The solution discussed extensively in this thread requires no VBA.

[macropod]

I agree that this works and I had no problem getting it to work with only one field in the cell.

However, because it uses text highlighting and not cell background shading, it does not work with additional text outside the field code, which was pointed out and which is what I was hoping to solve by asking for help in this forum. I apologize if my question was not clear.
Can you put your additional text inside the field code?
Charles: All you're doing here is replying to a question that was answered years ago... See posts #21 & #23.