Results 1 to 10 of 10
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    [NOT YET CLOSED] sort into rainbow order; Excel2007+

    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
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-14 at 17:15.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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: RG-rgb-colours-sorted.xlsm

    HTH L
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Zeddy,

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

    Maud
    Attached Files Attached Files

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-06-14)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

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

    Now I'm even more confused than I was before.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-06-14)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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
    Last edited by Maudibe; 2015-06-14 at 16:44.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-06-14)

  9. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  10. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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: RG-rgb-colours-sorted.xlsm

    I'm calling it a day!

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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 A2143 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

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud/RG

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

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •