Consulting

Results 1 to 9 of 9

Thread: Colour coding a table based on values

  1. #1

    Colour coding a table based on values

    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

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

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

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

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,294
    Location
    See Conditionally Format Mailmerge Output and Conditionally Shade Table Cells in the Mailmerge Tips and Tricks thread at:
    https://www.msofficeforums.com/mail-...ps-tricks.html
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,294
    Location
    Cross-posted at: https://www.msofficeforums.com/mail-...ut-ranges.html
    Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7

    Colour coding a table

    Quote Originally Posted by macropod View Post
    See Conditionally Format Mailmerge Output and Conditionally Shade Table Cells in the Mailmerge Tips and Tricks thread at:
    https://www.msofficeforums.com/mail-...ps-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

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,294
    Location
    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.
    Last edited by macropod; 03-26-2021 at 08:17 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,294
    Location
    Now also cross-posted at: https://answers.microsoft.com/en-us/...f-3366ca40fb45
    Kindly read the forum rules. Keep ignoring them and you'll find your account here terminated.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •