Let’s set up a sample database so we can dive into learning about conditional formatting. For the purposes of this article I created a new database based off the Desktop Asset Tracking template available within Access 2013.
Once I created the database I entered in some sample data (Figure 1).
Formatting Based on Data within the Control
For our first example we’ll highlight values in the “Current Value” field. If the value is under $100 we’ll highlight the value in red.
1. Click the field heading. In this case we’ll click the Current Value heading. This will select all rows under the Current Value heading.
2. Right click on Current Value and select Conditional Formatting.
3. The Conditional Formatting rules manager will open. Click New Rule.
4. For the rule type select the first open to check values in the current record.
5. Next we’ll build an expression to determine when we highlight certain values. Make sure “Field Value Is” is selected. The middle field should say “less than” and the third field will be “100.”
6. Next, we need to select our formatting options. For this example we’ll just use a red highlight. Note that you will see a preview of what the formatting will look like (Figure 2).
7. Click OK to return to the Rules Manager. Click OK again.
As you can see in Figure 3 our rule is applied and items less than $100 are highlighted.
Formatting Based on Data within a Different Control
For this example we’ll highlight the Item field if the condition field is either Poor or Bad. We’ll color the field Orange if “Bad” and Red if “Poor.”
1. As we did previously, we’ll highlight the Item field.
2. Right click the Item field and select Conditional Formatting.
3. Click New Rule.
4. Click the first field under “Format only cells where the:” and select “Expression Is.”
5. In the expression field we need to compare the Condition field to the values we’re seeking – “(4) Bad” in this case. Type in [Condition]=”(4) Bad”, click OK.
6. Select the appropriate formatting and click OK to return to the rules manager.
7. Repeat the process for the “(5) Poor” condition (Figure 4).
You can see two of our items have been highlighted based on the contents of a different field (Figure 5).
Format Focused Controls
For our last example we’ll change the way a control that has focus looks. When you click on a field or use the TAB button the control you land on has the “focus.” We’ll change the way focus looks for the Asset Details form.
1. Right click Asset Details and select Design View.
2. While holding down SHIFT, left click on each of the controls.
3. Under the Form Design Tools -> Format tab select Conditional Formatting.
4. Add a new rule.
5. Select “Field Has Focus” from the “Format only cells where the:” section.
6. Select your formatting options and click OK.
Now when we tab over our controls you can see they will highlight with my formatting selection (Figure 6).
There is a lot you can do with conditional formatting. Don’t forget you can use conditional formatting in both forms and reports to help you better visualize and understand the data in your database.