Conditional Formatting in Access Forms and Reports

Conditional formatting makes important data stand out automatically. Overdue invoices turn red. High-value orders turn green. Records requiring attention are bolded. Users can scan a form or report and immediately see what needs their focus — without reading every value.

Setting Up Conditional Formatting

Conditional formatting is available on text boxes and combo boxes in both forms and reports.

1. Open the form or report in Design View
2. Select the control you want to format
3. Go to Format → Conditional Formatting on the ribbon
4. Click New Rule
5. Configure the rule and click OK

Rule Types

Field Value Is — compares the control's value to a threshold:

• Greater than, Less than, Equal to, Between, etc.
• Example: Field Value Is greater than 1000

Expression Is — evaluates a custom expression that returns True or False:

• Can reference other controls on the form
• Example: [DueDate] < Date() And [Status] <> "Paid"

Field Has Focus — applies formatting when the cursor is in the field (forms only)

Compare to Other Values — compares the value to other values in the same field (reports only, Access 2010+)

Formatting Options

For each rule, you can set:

• Font — bold, italic, underline
• Font color — text color
• Background color — fill color
• Enabled/Disabled — grey out the control

Practical Examples

Highlight Overdue Records

On an invoices form, highlight the DueDate field red when the invoice is overdue:

• Rule type: Expression Is
• Expression: [DueDate] < Date() And [Status] <> "Paid"
• Format: Background color = red, Font color = white

Color-Code Status Values

On an orders form, color the Status field based on its value. Add three rules:

1. [Status] = "Pending" → Yellow background
2. [Status] = "Shipped" → Blue background, white text
3. [Status] = "Delivered" → Green background, white text

Highlight High-Value Orders

On an orders report, bold and color the OrderTotal field for large orders:

• Rule: Field Value Is greater than 10000
• Format: Bold, green background

Flag Missing Required Data

Highlight a field red when it is empty and the record has been saved:

• Rule: IsNull([Email]) And Not IsNewRecord()
• Format: Red background

Data Bars (Access 2010+)

Data bars display a horizontal bar inside the control proportional to the field's value — like a mini bar chart within each cell. They are available in reports.

To add a data bar:

1. Select the numeric control in the report
2. Format → Conditional Formatting → New Rule
3. Select "Compare to other values"
4. Choose "Data Bar"
5. Set the minimum and maximum values (or use "Lowest value" and "Highest value")
6. Choose the bar color

Data bars give users an immediate visual sense of relative magnitude without needing to read every number.

Multiple Rules and Priority

You can add multiple rules to a single control. Rules are evaluated in order from top to bottom — the first matching rule wins.

To reorder rules, use the up/down arrows in the Conditional Formatting Rules Manager. Place the most specific rules first and the most general rules last.

Conditional Formatting with VBA

For more complex formatting that cannot be expressed with the built-in rules — like formatting based on a value in a different table, or applying formatting to multiple controls at once — use VBA in the form's Current event:

Private Sub Form_Current()
    ' Color the entire row based on Status
    Dim bgColor As Long
    
    Select Case Me.Status
        Case "Overdue"
            bgColor = RGB(255, 200, 200)  ' Light red
        Case "Pending"
            bgColor = RGB(255, 255, 200)  ' Light yellow
        Case "Complete"
            bgColor = RGB(200, 255, 200)  ' Light green
        Case Else
            bgColor = RGB(255, 255, 255)  ' White
    End Select
    
    ' Apply to all detail controls
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Section = acDetail Then
            On Error Resume Next
            ctl.BackColor = bgColor
            On Error GoTo 0
        End If
    Next ctl
End Sub

Formatting Based on Another Field

The built-in conditional formatting only evaluates the control's own value. To format based on another field, use an Expression Is rule:

• Control: txtAmount
• Rule: Expression Is [Priority] = "High"
• Format: Bold, red text

This bolds and colors the Amount field whenever the Priority field is "High" — even though the rule is on the Amount control.

Performance Considerations

Conditional formatting with complex expressions can slow down forms with many records. If performance is an issue:

• Use simple Field Value comparisons instead of complex expressions where possible
• Limit the number of rules per control
• For reports, conditional formatting is evaluated at render time and has less impact on interactive performance

Conclusion

Conditional formatting is one of the highest-impact, lowest-effort improvements you can make to any Access form or report. A few well-chosen rules — overdue dates in red, completed items in green, exceptions bolded — transform a wall of data into a scannable, actionable interface. Set it up once and it works automatically for every record, every time.