Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by color (makro)
How can i sort a list by the color in the line. I want all green lines on
top then all red lines then yellow ones. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by color (makro)
Philipp,
See http://www.cpearson.com/excel/sortbycolor.htm for examples. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Philipp Oberleitner" wrote in message ... How can i sort a list by the color in the line. I want all green lines on top then all red lines then yellow ones. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by color (makro)
do you mean by the background color of the cells in the row?
Are there only going to be Green, Red and Yellow ones. Which specific colorindexes? (there are shades of Green and Yellow). -- Regards, Tom Ogilvy "Philipp Oberleitner" wrote in message ... How can i sort a list by the color in the line. I want all green lines on top then all red lines then yellow ones. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by color (makro)
yes the background color i specified them in rgb
like this RGB(128, 255, 196) RGB(255, 128, 128) RGB(255, 255, 170) Thanks alot "Tom Ogilvy" schrieb im Newsbeitrag ... do you mean by the background color of the cells in the row? Are there only going to be Green, Red and Yellow ones. Which specific colorindexes? (there are shades of Green and Yellow). -- Regards, Tom Ogilvy "Philipp Oberleitner" wrote in message ... How can i sort a list by the color in the line. I want all green lines on top then all red lines then yellow ones. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort list by color (makro)
Sub Macro5()
Dim a(1 To 3) As Long Dim b(0 To 56) As Long a(1) = RGB(128, 255, 196) ' Green - Rank 1 a(2) = RGB(255, 128, 128) ' Red - Rank 2 a(3) = RGB(255, 255, 170) ' Yellow - Rank 3 Columns(1).Insert Range("A1").Interior.Color = RGB(128, 255, 196) Range("A2").Interior.Color = RGB(255, 128, 128) Range("A3").Interior.Color = RGB(255, 255, 170) b(Range("A1").Interior.ColorIndex) = 1 b(Range("A2").Interior.ColorIndex) = 2 b(Range("A3").Interior.ColorIndex) = 3 Set rng = Range(Cells(1, 2), _ Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng cell.Offset(0, -1).Value = _ b(cell.Interior.ColorIndex) Next rng.EntireRow.Sort Key1:=Range("A1"), _ Order1:=xlAscending Columns(1).Delete End Sub This keys of the color in Column A and assumes your data starts in A1 and all rows should be sorted. If you had an uncolored header row it would be sorted to the top. It also assumes that you have values in the cells in Column A. the code inserts a new column A and assigns ranking numbers in each cell, then sorts on those and removes the new column A. -- Regards, Tom Ogilvy "Philipp Oberleitner" wrote in message ... yes the background color i specified them in rgb like this RGB(128, 255, 196) RGB(255, 128, 128) RGB(255, 255, 170) Thanks alot "Tom Ogilvy" schrieb im Newsbeitrag ... do you mean by the background color of the cells in the row? Are there only going to be Green, Red and Yellow ones. Which specific colorindexes? (there are shades of Green and Yellow). -- Regards, Tom Ogilvy "Philipp Oberleitner" wrote in message ... How can i sort a list by the color in the line. I want all green lines on top then all red lines then yellow ones. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
How do I sort my data by color? (color applied to rows) | Excel Worksheet Functions | |||
Sort or sub-total by Fill color or font color | Excel Discussion (Misc queries) | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Excel-Makro for automatically generated protocol out of todo-list | Excel Programming |