Hack 30. Use Conditional Formatting to Point Out Important Results
Why not add a little impact to important results or facts about your data? Instead of having a report display results textually, use a bit of formatting based on conditions in the data to draw readers' eyes directly to the important news. If the news is good, you can take all the credit and maybe get a promotion. If the news is bad, you can always use the "Don't shoot the messenger" line.
"Create Conditional Subtotals" [Hack #29] demonstrates how to create a report based on data that covers two years, with each year broken out as its own total. This is great for common analyses in which you're comparing results from one year to the next to see how much the data has changed (including whether the change was positive or negative).
Some reports, however, also print a third column indicating the percent change when the two values are compared. Although this isn't covered here, you can apply the conditional formatting explained in this hack to the percent change text boxes if you choose to include them.
4.4.1. Standard Conditional Formatting
Access provides a nice conditional formatting utility. With it you can easily change font attributes, foreground color, and background color properties when a specified condition is met. Figure 4-11 shows the Conditional Formatting dialog box. In this example, expressions have been entered for the conditions. Alternatively, you can base the conditions on actual data values.
Figure 4-11. Font colors that change based on the condition
Use the Format Conditional Formatting… menu to display the Conditional Formatting dialog box. The Conditional Formatting dialog box manages formatting for one control at a time. Therefore, you must select a control before you can access the menu. Also, the menu item is disabled unless conditional formatting can be applied to the selected control.
Figure 4-11 shows the conditional formatting that has been set up for the txtCT2004Total text box. In particular, for this control the following three formatting options have been set, to test for the difference in percentage between the 2003 and 2004 amounts:
Each condition provides different formatting based on selections made in the Conditional Formatting dialog box. This works fine, but the three-condition limit might require another approach.
4.4.2. Conditional Formatting the VBA Way
By placing code into the report's event stubs, you can provide robust formattingbeyond what the standard conditional formatting feature allows. The standard formatting has two major limitations: you can test for three conditions only, and some of the formatting options aren't available.
The workaround is to just code up your own using VBA. Figure 4-12 shows the report; note that Total Visits for 2004 is set in italic and has a border around it.
Figure 4-12. Conditional formatting applied through VBA code
This formatting was applied because a condition was met, based on what was tested in the code. This code has been placed in the report's ReportFooter_Print event:
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) Dim visits_change As Single visits_change = ([txt2004Total] - [txt2003Total]) / [txt2003Total] Select Case visits_change Case Is >0.25 Me.txt2004Total.Properties("ForeColor") = vbBlue Me.txt2004Total.Properties("Borderstyle") = 1 Me.txt2004Total.Properties("BorderColor") = vbBlack Me.txt2004Total.Properties("FontItalic") = 1 Case Is <= 0.25, Is > 0.2 Me.txt2004Total.Properties("ForeColor") = vbBlue Case Is <= 0.2, Is > 0.15 Me.txt2004Total.Properties("ForeColor") = vbGreen Case Is <= 0.15, Is > 0.1 Me.txt2004Total.Properties("ForeColor") = vbMagenta Case Is <= 0.1, Is > 0 Me.txt2004Total.Properties("ForeColor") = vbBlack Case Is <= 0 Me.txt2004Total.Properties("Borderstyle") = 1 Me.txt2004Total.Properties("BorderColor") = vbRed End Select End Sub
The code tests the percentage change and then uses a Select Case statement to apply different formatting based on the percentage change. Six conditions are provided, but you aren't limited in terms of number of conditions; use whatever number makes sense for your application. Also, the type of formatting is open to whatever you can control through VBA, which is just about everything.