Version 2.22         Approx 0.6 MB                 First Published 27 Oct 2021                 Last Updated 18 Sept 2025

This is a companion to the Colour Converter / Selector example application.

Conditional formatting was added in Access 2002. Originally, users were limited to 3 conditional formats (or 4 colours including the default colour).
This limit applied whether using VBA or the conditional formatting wizard.

With the release of Access 2010, a significant improvement was made with up to 50 colour formats available using the conditional formatting wizard.
However, the 3 colour limit still applied using VBA in Access 2010 though various workarounds were possible.
The limit was belatedly also increased to 50 conditions using VBA with Access 2013.

Many years ago, partly to overcome the original limits for conditional formats, I developed an alternative approach.

An unlimited number of colour formats can in fact be obtained without using conditional formatting. Instead, the colour formats are stored in a lookup table.

The idea of using colour values stored in a table remains just as useful now that the earlier 3 condition limit no longer applies.



Example App

The example app demonstrates the use of this approach for formatting student test scores.


The test scores are displayed with their background and foreground colours depending on the value.

The colours are specified in 10% ranges from 0 to 100% (but any set of values can be used as required).


A colour picker dialog can be used to set each of the colours.


This approach will work in any Access version from Access 2000 onwards (32-bit or 64-bit).



Download

The attached zip file includes both ACCDB & MDB (2000) formats

Click to download:     Colour Conditions     Approx 0.6 MB  (zipped)



Feedback

Please use the EMail button in the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any bugs in the application.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 18 Sept 2025

Return to Example Databases Page

Return to Top