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:

mergefields.jpg

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-m...ps-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:
mergefield condition code.jpg

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

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.
desired output.jpg

[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.

Capture1.jpg
Capture2.jpg
Capture3.jpg

[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?


Attachment 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.

mailmergeformat.jpg

[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.

2019-04-19_17-11-00.jpg

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.