Alternative to conditional formatting if you have 1,000 variations

[plymouth]

I have a table right now lest say 30 fields with 5 records.


So for each data item in each field in each record I want the background of that of that data item to be a color specified by a function (maybe 5 -10 lines of code not written yet) the possible colors = about 200 - 300 possible different colors tat the function will determine is appropriate.

I know how to use conditional formatting but to have 200 or 300 or even 1,000 different rules for each of the 30+ fileds is ridicilious and what happen if the colors change in the future I would have to update 30,000 conditional rules. That's clearly not a reasonable strategy.

So how can I have a table of 30 fields and 5 or 10 records (or maybe even more) display where each of the 120 or 300 data elements is a different color based upon the calculations from the output of a function?

Right now the table is a local table populated by some code but in the future it might be a crosstab table.

BTW I am using Access 2019/365 32 bit on a Windows 10 Pro machine

[CJ_London]

Do you really think you are going to be able differentiate between 200-300 colours?

Whether you can or can't, what I do rather than conditional formatting is to use rich text which enables you to use colours plus other text formatting. The richtext codes are added in your recordsource query, either hardcoded or by referencing a metadata table.

the information you provided is not detailed enough to provide a full suggestion or how it might work, but a hardcoded version in the query builder might look something like

fld1formatted:"<font style=''BACKGROUND-COLOR:" & switch([fld1]<10,"#FFFF00",[fld1]<20,"#FFF123") & "''>" & [fld1] & "</font>"

needless to say, your numerical values will be converted to text, which may or may not matter

I note you are talking about tables, conditional formatting applies to forms

[isladogs]

Whilst I also have doubts about the practicality of this for several hundred different items, you could try out the Colour Categories approach used in this example app.
http://www.mendipdatasystems.co.uk/c...ter/4594450413



In theory you could have millions of different colour categories.