Conditional Formatting with increasing colors

Question

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.

Answer 1

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).