P
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.
top then all red lines then yellow ones.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
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
See http://www.cpearson.com/excel/sortbycolor.htm for examples.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
T
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).
Are there only going to be Green, Red and Yellow ones. Which specific
colorindexes? (there are shades of Green and Yellow).
P
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
like this
RGB(128, 255, 196)
RGB(255, 128, 128)
RGB(255, 255, 170)
Thanks alot
T
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.
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.