Sort by condition

Here's one way to sort by cell color

Here is one way to sort by cell shade...there are other ways probably more elegant but this works for me. Modify sheet name, range, and column for temporary color index ID (I use column B here, assuming shaded cells are in column A, from row 1:25). I included some notes to show why the code does what.

Sub SortColor()
' Macro by Thomas Urtis

'The following code sorts shaded cells in a single column range
'based on Excel's color index number. Modify the worksheet name,
'colored cell range, and temporary offset reference as needed.

Application.ScreenUpdating = False
'Select the sheet and range with shaded cells
Worksheets("ColoredCells").Range("A1:A25").Select
'Declare variable; ID color in adjacent right cell
Dim Color As Range
For Each Color In Selection
Color.Cells(1, 2) = Color.Cells.Interior.ColorIndex
Next
'Sort by color number
Range("A1:B25").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Clear color number, as it is not actual worksheet data
Range("B1:B25").ClearContents
'Finish in A1 or wherever you want
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Any help?

Tom Urtis

Thanks, but...

Thats great,, it throws back at me the number 41 and then when i go back to my sheet all the cells have got -4142 next to them, i presume (?) that this is because the cell has the same number but the conditional formatting is giving a differnt code?

any ideas

thanks

Re: Thanks, but...

I'm not sure`what you mean by the code "throwing back" a number, or you going back to your sheet, or "all the cells have got -4142 next to them". Did you try the code? If so did you modify the range, sheet name, and open available column (I used col B in the code example but which column is best for you depends on your spreadsheet).

If you are`still experiencing a problem, please repost. Also, if you used conditional formatting to determine cell color, you could always sort opr filter the cells by the condition that causes them to be green, and that might be the easiest way for you to go.

Tom Urtis

Re: Thanks, but...

sounds like you may have a different solution to the one i am attempting. if i can explain my problem.

I have 4 columns with the following headers:

Customer, 1999, 2000, 20001

Underwhich i have the customer name and how much they spent in each of the years.

I then have a 5th column to the right of 2001, which calculates the average sales over the past 3 years i.e. =average(b2:d2).

I have then applied conditional formatting to this column, with the following rules:

if average sales > 2001, highlight in red....if average sales < 2001, highlight green.

i have another set of conditional formatting on the column headed 2001 which throws out a different colour if 2001 sales < 2000 sales.

all of which i need to sort, so i can put into there own report

thanks for you help

rich

A few questions please

Thanks for this info...now I understand more of what you are needing.

A few questions just to keep from sending you superfluous code:

(1) What is your range...maybe from A1:E?, A1:E1 being header rows? Do the number of rows stay constant or do they change from time to time?

(2) Are there only 3 colors involved (red and green for col E, and another for col D as you say)?

(3) How many of the 5 columns do you have any kind of color coding for?

(4) Is there data in the worksheet in any column past column E (or the used range) that you need to keep left alone? Or are columns F:IV not used?

The purpose of the questions is to not send you code that will conflict with other items on your worksheet.

The info in your last post was great, I just need a little more and we can make this happen.

Tom Urtis

Here you go, many thanks

Tom, I hope that this info is of the calibre needed. Thanks loads for your time and brain skills!

The range is A1:E604 - the numbers of rows will change.
There are only 3 colours as it stands at the moment!!
I colour code in columns D and E, D being 2001 figures and E average.
Columns F:IV are free

thanks again
rick