Hack 30. Use Conditional Formatting to Point Out Important Results

Not only can you use the built-in conditional formatting feature, but you also can roll your own with a little VBA!

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:

Greater than 20%

   ([txtCT2004]-[txtCT2003])/[txtCT2003]>0.2

Greater than 15% and equal to or less than 20%

   ([txtCT2004]-[txtCT2003])/[txtCT2003]<=0.2 And
        ([txtCT2004]-[txtCT2003])/[txtCT2003]>0.15

Greater than 10% and equal to or less than 15%

   ([txtCT2004]-[txtCT2003])/[txtCT2003]<=0.15 And _
        ([txtCT2004]-[txtCT2003])/[txtCT2003]>0.1

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.