Sort list by color (makro)

[Philipp Oberleitner]

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.

 

[Chip Pearson]

Philipp,

See http://www.cpearson.com/excel/sortbycolor.htm for examples.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

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

 

[Philipp Oberleitner]

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]

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.