3

I have a list of repeating table names: 

Table1
Table1
...
Table2
...
Table99

I was wondering if there was anyway to use Excel conditional formatting to highlight each different table name a different color using some sort of increment.

So table1 would be 000033 and table2 000066 and so on using the hex value

I can write the conditional to say if R <> R-1 then new color or if R=R-1 then same color, but it is the color part that has me stumped.

I can write a VB function if needed but I was looking for a quicker solution.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Mike
  • 5,918
  • 9
  • 57
  • 94
  • The color is not the only problem. There is another one: conditional formatting in Excel 2013 only supports up to 5 colors (natively). If you use the formulas then you could push it up to 8 colors. But looking at your sample data it seems to me that you are looking for many more colors (will be a very colorful sheet). But in the end you are right: all formatting is "hard coded" into the formatting condition and is not dynamic. The only possibility is to use a `2-Color Scale` or `3-Color Scale`. In such cases the color is dynamically assigned. But you couldn't give it a particular hex-value. – Ralph Jun 13 '16 at 13:44
  • Maybe I can use a rotating 3 colors, so if <> and color is green then red... something like that but how can I get the backcolor – Mike Jun 13 '16 at 14:11
  • That's for you to decide. Only you know the requirements. :) – Ralph Jun 13 '16 at 14:13
  • 1
    @pnuts I was talking about the possible colors per conditinal formatting rule. Of course, if you stack a couple hundred rules one over the other then there are many more possibilities. But then you're running into the next problem of managing / tracking all of these rules and color coding schemas (which one goes first and after which to stop etc...). – Ralph Jan 30 '18 at 15:11

1 Answers1

2

You might cycle through three colours (provided the numbers for your Tables are sorted in numerical order) with CF formula rules such as:

=MOD(SUBSTITUTE(A1,"Table",""),3)=1

and =1 replaced by =2 and =0 for the other colours.

If you really want more colours (say 10) change the 3 to 10 and add rules for =3 etc. All 99 should be possible (though IMO not necessary).

pnuts
  • 58,317
  • 11
  • 87
  • 139