Setting Column-Specific Colors in Microsoft Access List Boxes and Combo Boxes

How to use the Format property of a query or table field to set custom colors for columns in a Microsoft Access list box or combo box control.

Setting Column-Specific Colors in Microsoft Access List Boxes and Combo Boxes

Did you know that you can set custom colors for specific columns in list boxes and combo boxes in Microsoft Access?

Karl Donaubauer covered the details in his "Baker's Dozen of Tips & Tricks."  It's tip #13 and begins at the 40:15 mark of the presentation:

Here are the notes from Karl's slide:

In the Format property of tables and queries, you can specify colours. These colours are also inherited in list and combo boxes. This enables coloured highlighting of individual columns or values.

• version-dependent/moody: works in 97, 2007, 2010, 2013, in 365 in list boxes only if primary key is present in the query, in combos only with an (arbitrary) setting in the Format property of the combo (e.g. @)
• only works with text and memo fields
• only works with tables and saved queries, SQL in the data origin (usually) does not remember formatting
• possible colours: black, blue, green, cyan, red, magenta, yellow, white

Karl shows a sample list box with the columns colored:

Making it Work: The Format Property

To make this work, you use the color names as part of the field's Format property:

Here are the formats that Karl used in his example above:

   Source: IIf([Remuneration]>=1000,"Expensive")
   Format: @[Red];"Cheap"[Green]
   Source: Switch([Rating]=1,"*§$%&*",[Rating]=5,"♥")
   Format: @[Red]
   Source: Country
   Format: @;"missing"[Magenta]

To download Karl's sample database, visit the resources page for the presentation on Colin Riddington's website: A Baker's Dozen of tips, tricks and tools in Access - Karl Donaubauer.

Richard Rost's Take

Access Video King Richard Rost covered this same topic in a video last year:

As is typical of Richard's style, he takes you through the process step-by-step.

External references

A Baker’s Dozen of tips, tricks and tools in Access
Karl Donaubauer (MVP) demonstrated 15 of his favourite tips, tricks and tools in 60 minutes. Karl is constantly collecting techniques, tools, solutions and explanations that you don’t hear and read everywhere. Even if you think you already know everything about Access, this session is guaranteed to …

All original code samples by Mike Wolfe are licensed under CC BY 4.0