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