Conditional Formatting In Word

NOTE: In Word, there is an overriding style attached to every paragraph, so any formatting changes made to paragraphs without changing the associated style may not stick. That’s when you might notice that your formatting changes do not work. To clear formatting from content, select the text for which you want to clear formatting. Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data. A conditional format changes the appearance of cells on the basis of conditions that you specify.

Conditional Formatting In Word

Word templates introduced in CRM Online and CRM 2016 are not the easiest contraptions to edit. One of the questions that popped up in a recent conversation was how to use conditional IF in the document generation.

Creating a Word Document Template (with conditional switching) Verified Since CRM isn't actually dynamically generating Word documents, just mapping data to the Word template, I don't think you're going to get what you need out of the box. Conditional Formatting in Microsoft Word 2010: Go to 'Replace', click in the box next to 'Find what' and type in your condition. (If your condition is more than just words and/or numbers, check in the 'Format' or 'Special' buttons to see if you can (or need to) more closely identify what you want Word to find.).

tl;dr

To refer to the control values after the document has been generated, wrap content controls using bookmarks defined at the control container level.

Longer

CRM fields are defined as Content Controls and in theory we could have written some simple VBA using ContentControl object. Except that CRM does not play nice when generating the document and simply replaces all content controls with the record values.

The solution is to wrap the content controls using the bookmarks defined at the container level, i.e. cell, row, paragraph, etc. Then this bookmark can be used as a reference in a conditional expression of the IF field.

For example, to insert conditional text depending on the total invoice value:

  1. Extract the Invoice template that comes with the sample data and save it under a different name.
  2. Open the template, find total amount, select the entire table cell and click Insert > Bookmark.
  3. Type totalbookmark as a bookmark name, click Add.
  4. Delete the words “Thank you for your business” and click Insert > Quick Part > Field.
  5. Select IF field and type the following field code:
    IF totalbookmark > 100 “Thank you for you business!” “Thanks for nothing!”
  6. Click OK. Hint: to see and edit field codes in the document, press Alt-F9.
  7. Save the file and upload to CRM as a new template.
  8. Create an invoice and generate the invoice document.
  9. Enable document for editing, press Ctrl-A to select the entire content and press F9 to refresh the field content
  10. Depending on the total value of the invoice, the document will say “Thank you for your business!” or “Thanks for nothing!”

It’s not a full automation that potentially could be improved with some VBA (I’m not 100% sure if it will work) but it’s a step in the right direction and a cheap way to add some pizazz to your documents.

  • 29 November 2019
  • Updated: 27 August 2020

It seems my conditional formatting posts have become quite popular and with that, I’ve had some amazing questions asked about variations of how to use conditional formatting. If you haven’t already taken a look at my other conditional formatting posts, be sure to explore how to use conditional formatting with dates based on a range, or conditional formatting based on dates earlier than today.

I now want to show you how to can use conditional formatting to highlight entire rows of text. The approach for this one is a little bit different but works amazingly.

First off I have my list of names along with dates that staff are due to renew their certifications. I am going to set up Excel so that it highlights the entire row of data when a date has passed todays date.

  1. Open the sample file you wish to use, or recreate a sample similar the one shown below:
  1. I have added the =TODAY() formula into cell F1 which I will reference in the conditional formatting. This also gives me the flexibility to easily change this date depending on my needs and the conditional formatting will automatically respond and change formatting where applicable.
  2. Now we are going to create the new conditional formatting rule on the first record only
  3. Highlight cells A2 to C2
  4. From the Home tab select Conditional Formatting > New Rule
  1. From the options, select Use a formula to determine which cells to format
  1. Now in the Format values where this formula is true, we need to create a formula which will check the dates for us
  2. Into the field type =$C2<$F$1
  3. Here I have used some absolute cell references to lock only the column letter. We will be copying this formatting to the other rows in our worksheet so I want the row numbers to be relative so that they change with each row we copy this to. I do not want the column letter to change. I have then locked the entire cell reference looking at our date cell in F1.
  4. Now click the Format button and apply either Font formatting, or Fill formatting options.
  1. Click OK once you have selected the formatting you wish to use
  2. Click OK to complete the rule

Conditional Formatting In Word Mail Merge

  1. Click OK again to close the New Formatting Rule window
  2. My first example record meets the criteria of being a date earlier than today so it has formatted accordingly (in gold)
  1. Now I need to use Format Painter to apply this same conditional formatting to the other records in my worksheet
  2. Highlight cells A2 to C2 and click the Format Painter button from the Home tab

Conditional Formatting In Word 2010

  1. Now select the remaining records in your worksheet where you would like to use the conditional format
  2. All records which meet the criteria should now be formatted accordingly

Conditional Formatting In Word 2013

  1. If you want to test out the formatting, change the date used in cell F1 and your record formatting should respond accordingly
  2. Have fun!

Every time I run an Excel course (which is often), I always say how much fun Conditional Formatting is. This just shows yet another genius way to use the program in a way us visually minded users can make our data look “pretty”. If you are Sydney based and interested in having me come to your workplace for some training feel free to make contact otherwise check out my other great Excel based articles here.

Conditional Formatting In Word Mail Merge

Formatting
  • Colour, conditional formatting, formulas
  • Microsoft Excel