#1
|
|||
|
|||
Is it possible to sort cells in shades of colors?
Welcome!
Is it possible to sort cells in shades of colors? thanks in advance. |
#2
|
|||
|
|||
Yes but you need to do it with VBA. Basically each of those colors has a color index # that you can sort by. You could use this code.
Code:
Sub FillColorIndex() 'Takes the index of a color and places it in the cell next to the data Dim ColorRow As Integer, TotalRows As Integer TotalRows = WorksheetFunction.CountA(Range("b:b").Rows) For ColorRow = 2 To TotalRows Range("d" & ColorRow).Value = Range("b" & ColorRow).Interior.ColorIndex Next ColorRow MsgBox "All Rows Complete" End Sub |
#3
|
|||
|
|||
If you're happy with RGB sorting?
See attached |
#4
|
|||
|
|||
Quote:
http://labs.tineye.com/color/001c85d...True&height=32 does an interesting job, but still no enough. |
#5
|
|||
|
|||
Upon further searching it appears that there may actually be a custom sorting option in your version of Excel.
http://spreadsheets.about.com/od/dat...olor-excel.htm I was not aware of this but looks like you can accomplish what you need quite easily afterall. |
#6
|
|||
|
|||
As I had not thought of repeatedly apply sorting for each color. Thank you for the correct path.
|
#7
|
|||
|
|||
rainbow but still does not work, will have his hands still do everything.
|
#8
|
|||
|
|||
Still? What have you tried that didn't work? I know that the VBA script gives a number you can sort by but maybe you were looking for a different kind of sort. Let us know thanks.
|
#9
|
||||
|
||||
Kreol2013: Ultimately, this is a question of what constitutes the sorting criteria. Simply say 'by shades of colors' is quite ambiguous. What are your criteria for 'shades of colors'? One could, for example, sort by the R, G or B values in the RGB colour scheme, or by the C, M, Y, K values in the CMYK colour scheme or by the H, S, L values in the HSL colour scheme. You need to work out what your criteria are according to one of these schemes. Compared to C, M, Y, K or H, S, L, it's quite easy to do R, G, B sorts because Excel works in the RGB colour scheme; conversions (for which algorithms are available) would be required for the others. Using the RGB colour scheme, for example, you could sort by Red, Green or Blue intensity, with one or more of the others as a secondary/tertiary criterion, each in ascending or descending order.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Hi!
Yes, eventually I stopped on the circuit HSV Hue sorting ascending Value sorting ascending Sat sorting descending Thank you sample code: Code:
Sub test() For i = 2 To 5000 sHexVal = Cells(i, 5).Value If (sHexVal <> "") Then Call RGBtoHSV(Cells(i, 6).Value, Cells(i, 7).Value, Cells(i, 8).Value, i) End If Next i End Sub Sub RGBtoHSV(Red, Green, Blue, y) Dim min As Double, max As Double, delta As Double If Red <= Green And Red <= Blue Then min = Red If Green <= Red And Green <= Blue Then min = Green If Blue <= Red And Blue <= Green Then min = Blue If Red >= Green And Red >= Blue Then max = Red If Green >= Red And Green >= Blue Then max = Green If Blue >= Red And Blue >= Green Then max = Blue Value = max delta = max - min If Not delta = 0 Then Sat = delta / max Else Sat = 0 Hue = 0 Cells(y, 9).Value = Sat Cells(y, 10).Value = Hue Cells(y, 11).Value = Value Exit Sub End If If Red = max Then Hue = (Green - Blue) / delta ElseIf Green = max Then Hue = 2 + (Blue - Red) Else Hue = 4 + (Red - Green) / delta End If Hue = Hue * 60 If Hue < 0 Then Hue = Hue + 360 Cells(y, 9).Value = Sat Cells(y, 10).Value = Hue Cells(y, 11).Value = Value End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to Sort Table Cells from Left to Right | tamaracklodge | Word Tables | 4 | 03-04-2013 04:00 AM |
Unable to change font colors from theme colors | choy | Word | 3 | 08-01-2012 09:12 PM |
Auto Colors in Cells? | Learner7 | Excel | 5 | 07-06-2010 10:55 PM |
How can I change the colors of cells automatically based on Job Completion? | Learner7 | Excel | 0 | 07-06-2010 10:47 PM |
Count range cells eliminating merge cells | danbenedek | Excel | 0 | 06-15-2010 12:40 AM |