Colour coding a table based on values

[E.Buttimer]

Hi

I hope someone can help!

I have been asked to create a label merge for some attendance data. The table is very simple and is a single row with four columns (Name, Previous week att. Current week att, difference). The data is imported from a excel spreadsheet.

What i needs is for column 2 and 3 to have scaled colour based on the following;

>96 Green
between 90 and 96 amber
<90 Red

Column 4

=0 amber
>0 green
<0 Red

The label merge needs to apply the above criteria to each of 36 tables created in the label merger.

I hope someone can help as i'm stuck! I am hoping to find a Marco solution

Thanks

Ed

[KOKOSEK]

Use Conditional formatting. It is exactly what you are looking for.

[E.Buttimer]

Sorry, its a Word mail merge, the conditional formatting inst an option in MS Word, unless I have missed something.

[KOKOSEK]

Sorry I've missed that question is a word related.

[macropod]

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

[macropod]

Cross-posted at: https://www.msofficeforums.com/mail-merge/46697-conditionally-colour-shade-numeric-output-ranges.html
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[E.Buttimer]

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


Hi,

I have already been looking at the above post, but I'm struggling to get either to work.

I was hoping to modify this to work with my values, but I can't see how it knows the ranges?

Conditionally Colour/Shade Numeric Output Ranges
You can use field coded to apply up to three colour/highlight formats to numeric data falling into various ranges. In the following examples, values up to 200 get coloured green, values over 240 get coloured red and values between these get coloured orange.

{QUOTE{=INT({MERGEFIELD Val}/40)-5 \# "{MERGEFIELD Val \* Charformat } - High';'{MERGEFIELD Val \* Charformat } - Low';'{MERGEFIELD Val \* Charformat } - Medium'}}

In regards the table shading trick, I don't that will work for me as one of by colours is based on a range rather than specific value.

Sorry if i'm not getting something, I am a totally newbie, but trying to learn to solve a request.

Regards

Ed

[macropod]

The field code representation you've posted is for shading the text only, not for shading the cell.

Contrary to your assertion, the cell shading can employ number range testing; the demonstration is only for showing how to apply the shading, not to tell you all the ways one might go about working out the values that determine the shading.

The link even includes a Basic Mailmerge Maths topic and a further link to my Microsoft Word Field Maths Tutorial. The field code representation you've posted demonstrates just one approach to determining such values.

As it is, the field code in the second example in the Conditionally Shade Table Cells topic is quite close to what you actually need. All you need to add is your own math for the conditions. A little bit of effort on your part on working out the logic wouldn't go astray.

[macropod]

Now also cross-posted at: https://answers.microsoft.com/en-us/msoffice/forum/all/conditional-formatting-with-colour-in-a-word-table/4e124af9-1d96-4259-afef-3366ca40fb45
Kindly read the forum rules. Keep ignoring them and you'll find your account here terminated.