How to: Format cells conditionally

Highlight values that need attention: bold prices above a limit, red rows below minimum stock, bars that compare amounts, and icons for low, medium, and high values.

Requires WKL Forms Object Library 1.23 or later.

FormatRules.Add creates a rule for a column's value: a comparison (wrkValue), a data bar (wrkDataBar), an icon set (wrkIconSet), or, from release 1.62, a color scale (wrkColorScale). FormatRules.AddExpression creates a rule with an expression in Access syntax, for one column or for the whole record. The returned rule holds the format (ForeColor, BackColor, Bold, Italic, Strikeout). Rules don't need an event and don't make any calls into VBA.

With Me!List1.Object.FormatRules
    .Clear
    .Add("Price", wrkValue, wroGreater, 30).Bold = True
    .Add("Price", wrkValue, wroBetween, 10, 20).BackColor = RGB(255, 242, 204)
    With .AddExpression("[Stock] < [MinimumStock]")      ' no column: whole record
        .ForeColor = vbRed
        .StopIfTrue = True                               ' later rules are skipped for this record
    End With
    .Add("Quantity", wrkDataBar).BarColor = RGB(91, 155, 213)
    .Add("Margin", wrkIconSet).IconSet = wisTrafficLights
End With

• Rules are checked in collection order. If two rules set the same format, the first one wins; StopIfTrue ends the check for that cell.
• Expressions use field names in square brackets and the functions available in Access expressions.
• Data bars and icon sets scale across the column's loaded values: the bar length reflects the position between the minimum and the maximum, and the icon is chosen by thirds (low, medium, high).
• Enabled = False turns a rule off without removing it.
• For formatting that depends on data outside the record, use CellFormat with FormatEvents = True, which makes one call into VBA per visible cell.

• API reference: WklFormatRules, WklFormatRule
• How to: Show values as a heat map
• The form becomes slow after handling NodeFormat or CellFormat
• WklTreeList overview