Sorting on colors?

[Erwin]

Hi all.

I receive every now and then a spreadsheet of someone which have some colors
in it. What I like to do is to sort the range on colors. As the range is
never the same as well I would like to name the range to sort in cell A1

Must be a task for you guys and girl.....I can't handle it.

Thanks in advance.

Erwin.

[David McRitchie]

Hi Erwin,
If the color is derived from Conditional Formatting you
would use the same formulas, except that you assign
a value, as you used for the C.F. and sort on assigned values.

If the color is derived from someone coloring the cell you
will have to run a macro use a function & recalculate the sheet,
to find the colorindex values and use that or some substituted
formula value for sorting.

See my colors.htm page for some additional help, but the
following function will return the interior color of another cell
that is from normal formatting not from C.F.

Function showColorIndex(rcell)
showColorIndex = rcell.Interior.ColorIndex
End Function

=showcolorindex(A28)

And you will need to use the following shortcut key when you want
to work with the numbers, because changing formats will not
trigger normal cell recalculation.

Ctrl+Alt+F9 Recalculates all cells on all worksheets in all open workbooks.

more information on colors:
http://www.geocities.com/davemcritchie/excel/colors.htm
more information on short cut keys:
http://www.geocities.com/davemcritchie/excel/shortx2k.htm
information on Event macros:
http://www.geocities.com/davemcritchie/excel/event.htm

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Erwin" <erwin...@hotmail.com> wrote in message
news:cJHF6.20538$Kt6.4...@amsnews03.chello.com...

[Erwin]

Hi David,

I'm sorry to ask you but can you please be a little more specific and maybe
answer with a little macro were you sort coloms A on collor in the selection
you choose...

Thanks in Advance.

A newbee in macro land. Erwin.

David McRitchie <DMcRi...@msn.com> schreef in berichtnieuws
um#2koezAHA.2012@tkmsftngp03...

[David McRitchie]

Hi Erwin,
To use the more efficient sorting of Excel as opposed to trying
to do your own sort, you have to include a column on your
sheet to include a sort sequence for the number. It probably
wouldn't be the color index. Perhaps by light tint of Green (1),
no color added(2), Blue(3),Yellow(4), Red(5).
Col H will get data from macro, based on interior color of
column A. Row 1 assumed to have
column headings so is not included in sort.

Sub Sort_on_important_coloring()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim vColor As Integer, cColor As Integer, i As Long
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
cColor = Cells(i, 1).Interior.colorindex
vColor = 0
Select Case cColor
Case 35
vColor = 1
Case 34
vColor = 2
Case -4142
vColor = 3
Case 36
vColor = 4
Case 38
vColor = 5
Case Else
vColor = 1000 + cColor
End Select
Cells(i, 8) = vColor
Next i
Cells.Sort Key1:=Range("H2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Erwin" <erwin...@hotmail.com> wrote in message

news:ZukG6.25260$Kt6.5...@amsnews03.chello.com...