sort into rainbow order; Excel2007+

Topic

New Reply

Hi everyone.

I have an attached file of vba ‘rgb color constants’.
They are sorted by alphabetic vba name, and also by ‘color number’.

What I would really like is to have these sorted in ‘rainbow’ order, i.e. from ‘black’ to ‘white’.
..so all the ‘greens’ are together etc etc etc,

Maud has given me a very nice ‘rainbow’ chart (also attached), but I would still like to see these particular rgb constants sorted in ‘color order’

zeddy

Reply | Quote

Replies

Zeddy,

That’s a tough one! I did a little googling and almost lots what little sense I have left.

However, I did manage to find some Java code that I converted to VBA to get at least a partial sorted list & attach the VBA names to it via Vlookup from your table.

You can probably get a fuller rainbow if you adjust the frequencies but that is getting beyond my understanding of the problem.

Test File: 41019-RG-rgb-colours-sorted

HTH :cheers:L

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Zeddy,

Different approach to RG’s fine code but I think I got the full spectrum.

Maud

Reply | Quote

Zeddy & Maud,

I just found something very interesting!

Neither my solution nor Maud’s solution generates all the codes!
I modified my workbook to included Maud’s nice code on sheet 3 (FullSpectrum) and include the lookup of the color names. Not many matched. :confused:

So I did a little investigating.
My code only came up with 62 of the color names (when I adjusted the code for exact matches only).
Maud’s code only came up with 23.
While Zeddy’s table had 142 names?

I picked a color at random and did a whole workbook search and it only showed up on Sheet1 (Zeddy’s sheet).
41021-Colors

Now I’m even more confused than I was before. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

There are 255^3 or 16,581,375 different combinations. I don’t think any of our codes generated that many combinations or colors. For example my code generated only 1792 different color combinations, RG was 402, Zeddy was 143. I believe we are all just looking at a different subset of color values

Reply | Quote

Hi RG and Maud

Amazing!
I really liked the rainbows in both of your files.
It has given me some ideas.
My question is still open though – I want to re-sort the 142 named constants by rainbow colour.

zeddy

Reply | Quote

Zeddy,

I tried this code:

Code:

Sub Really()

   Dim lCnt  As Long
   Dim lCntr As Long
   
   lCnt = Rows.Count - 1
   [a1].Select
   
   Application.ScreenUpdating = False
   
   For lCntr = 0 To lCnt
   
      ActiveCell.Offset(lCntr, 0).Interior.Color = lCntr

   Next lCntr
   
End Sub   'Really

It errored out with “Too Many Cell Formats” at row 63991! So it looks like there is no way to get the whole spectrum unless you are running the 64 bit version and maybe not even then as this version fell 984,585 formats short of being able to do a louzy 1,048,576 rows.

I don’t see a way to get the sort done since the numbers aren’t in the same ordinal order as the rainbow colors are in the rainbow.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hey Y’all,

Ok, I gave it another wack and another fail. I tried to use custom sort on the component parts of the RGB w/o any success. I then tried adding up to get a total and then using the component parts to break ties, still no luck.

Test File: 41023-RG-rgb-colours-sorted

I’m calling it a day!

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

One thing to keep in mind is that the order of the spectrum is not sequential color values. The values are in the following order:

1. rise in red (0,0,0) to (255,0,0)
2. Rise in green (255,0,0) to (255,255,0)
3, Decrease in red (255,255,0) to (0,255,0)
4. Rise in Blue (0,255,0) to (0,255,255)
5. Decrease in green (0,255,255) to (0,0,255)
6. Increase in Red (0,0,255) to (255,0,255)
7. Increase in green (255,0,255) to (255,255,255)
…..and everything between

This is why you cannot sort the spectrum simply by its values. I ran a 3 level nested loop with each color (255*255*255) following the mentioned order above using 16+ columns with one million values in each column and some change in the 17th column. As RG pointed out, there is a limit to the number of formats. So using the formula cellvalue=RGB(R,G,B) I filled each cell with the color Value, not interior color, resulting from the loop. These values are in spectral order going down 1 million rows then across 16+ columns. My intention was to match color values with zeddy’s list. I setup another 3 level loop to look for the search criteria of Zeddy’s values in column B looping in spectral order. My plan was for each pass through the loop would an index would increment indicating its order number. The order number was to be placed adjacent to the color value in column D Then Zeddy’s second value would be used as a search criteria returning an indexed number of loops or its place in the spectral order and placed adjacent to that value. Once cycled through the list, it would have been a matter of sorting A2:D143 using the indexed column D as the sort. I crashed after the 3rd search criteria in Zeddy’s list but I am confident it would have worked. Too much for my first generation OC core I7 with 12GB RAM I guess.

RG, maybe this would be an excellent benchmark test for your Haswell Core I7

Maud

Reply | Quote

Hi Maud/RG

..thanks for continuing to look at this.
..I’m still working on it too.

zeddy

Reply | Quote