- slide 1 of 6
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).
- slide 2 of 6
Formatting Based on Data within the Control
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.
- slide 4 of 6
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.”
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).
- slide 6 of 6
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.