Sort by Cells Interior Color Then by Date

[RyanH]

I have a spreadsheet that I use to keep track of ship dates. Currently if a
particular product is ready to ship I highlight the ship date cell by
changing its interior color = 31. All ship dates are in Column K. Is there
a way to sort the entire worksheet in this order:

1.) all .Interior.ColorIndex = 31 at the top
2.) then sort the dates of those cells in ascending order
3.) then sort all other dates after the .Interior.ColorIndex = 31 by
ascending order
4.) then sort those cells by Sales order number in Column A

Is this possible?

 

[Joel]

See if this code helps

Sub mysort()


'Move all with .Interior.ColorIndex = 31
'to beginning of worksheet

RowCount = 1
Do While Range("K" & RowCount) <> ""
If Range("K" & RowCount).Interior.ColorIndex = 31 Then
If RowCount <> 1 Then
Rows(RowCount).Cut
' ActiveSheet.Paste Destination:=Rows(1)
Rows(1).Insert Shift:=xlDown
End If
End If
RowCount = RowCount + 1
Loop

'Now find where end of highlighted cells are located
RowCount = 1
Do While Range("K" & RowCount).Interior.ColorIndex = 31
RowCount = RowCount + 1
Loop
firstNonHighlightedcell = RowCount
LastHighlightcell = RowCount - 1
Lastrow = Range("K" & Rows.Count).End(xlUp).Row

Set sortrange1 = Rows("1" & LastHighlightcell)
Set sortrange2 = Rows(firstNonHighlightedcell & ":" & Lastrow)

sortrange1.Sort _
Key1:=Range("K1"), _
Order1:=xlAscending, _
Key2:=Range("A1"), _
Order2:=xlAscending, _
Header:=xlGuess

sortrange2.Sort _
Key1:=Range("K" & firstNonHighlightedcell), _
Order1:=xlAscending, _
Key2:=Range("A" & firstNonHighlightedcell), _
Order2:=xlAscending, _
Header:=xlGuess

End Sub

 

[Dave Peterson]

I would use a helper column that would return the colorindex of the cell.

Chip Pearson has a routine that will do that:
http://cpearson.com/excel/colors.htm

Then I could sort my data by that column and the info in column A.

Remember that if you change colors, those UDFs that return the colorindex won't
recalculate. You'll want to force a manual recalculation before you trust the
results.

 

[RyanH]

Thanks for the quick response. I customized this to my application a bit,
but I am getting an error "Sort method of range class failed" labeled below:

Sub ColorSort()

Dim FirstNonHighlightedCell As Long, HighlightedCell As Long, LastRow As Long
Dim rngReady As Range, rngRegular As Range

Sheets("Global Schedule").Activate

'Move all with .Interior.ColorIndex = 8 (teal) to the top of worksheet
RowCount = 3
Do While Range("K" & RowCount) <> ""
If Range("K" & RowCount).Interior.ColorIndex = 8 Then
If RowCount <> 3 Then
Rows(RowCount).Cut
Rows(1).Insert Shift:=xlDown
End If
End If
RowCount = RowCount + 1
Loop

'Now find where end of highlighted cells are located
RowCount = 1
Do While Range("K" & RowCount).Interior.ColorIndex = 8
RowCount = RowCount + 1
Loop
FirstNonHighlightedCell = RowCount
LastHighlightcell = RowCount - 1
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set rngReady = Rows("3" & LastHighlightcell)
Set rngRegular = Rows(FirstNonHighlightedCell & ":" & LastRow)

rngReady.Sort _ '<== ERROR ERROR
Key1:=Range("K1"), _
Order1:=xlAscending, _
Key2:=Range("A1"), _
Order2:=xlAscending, _
Header:=xlGuess

rngRegular.Sort _
Key1:=Range("K" & FirstNonHighlightedCell), _
Order1:=xlAscending, _
Key2:=Range("A" & FirstNonHighlightedCell), _
Order2:=xlAscending, _
Header:=xlGuess

End Sub

 

[Joel]

Check the values of FirstNonHighlightedCell , LastHighlightcell , and
LastHighlightcell . I think you need to change RowCount = 1
to RowCount = 3. If your cells highlighted cells are start in row 3 there
may be a problem.

 

[RyanH]

After a little tweaking I got it to work. I have to code the rows to be
sorted like this:

Set rngReady = Rows(3 & ":" & LastHighlightcell)

For some reason this does not sort the rngReady.

Set rngReady = Rows("3" & LastHighlightcell)

I am new to VBA so I do not know the reason why, do you? Thanks for all
your help on this!

 

[Joel]

You are missing the colon (actually I was missing the colon in my original
code). Statement need to look like this Rows("3:27")

from
Set rngReady = Rows("3" & LastHighlightcell)
to
Set rngReady = Rows("3" & ":" & LastHighlightcell)

Also chage this line
from
Rows(1).Insert Shift:=xlDown
to
Rows(3).Insert Shift:=xlDown


The code may be better if you make the startrow a variable. Not sure if I
got all your changges into the code below.


Sub ColorSort()

Dim FirstNonHighlightedCell As Long, HighlightedCell As Long, LastRow As Long
Dim rngReady As Range, rngRegular As Range

Sheets("Global Schedule").Activate
StartRow = 3

'Move all with .Interior.ColorIndex = 8 (teal) to the top of worksheet
RowCount = StartRow
Do While Range("K" & RowCount) <> ""
If Range("K" & RowCount).Interior.ColorIndex = 8 Then
If RowCount <> StartRow Then
Rows(RowCount).Cut
Rows(StartRow).Insert Shift:=xlDown
End If
End If
RowCount = RowCount + 1
Loop

'Now find where end of highlighted cells are located
RowCount = StartRow
Do While Range("K" & RowCount).Interior.ColorIndex = 8
RowCount = RowCount + 1
Loop
FirstNonHighlightedCell = RowCount
LastHighlightcell = RowCount - 1
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set rngReady = Rows(StartRow & ":" & LastHighlightcell)
Set rngRegular = Rows(FirstNonHighlightedCell & ":" & LastRow)

rngReady.Sort _ '<== ERROR ERROR
Key1:=Range("K" & StartRow), _
Order1:=xlAscending, _
Key2:=Range("A" & StartRow), _
Order2:=xlAscending, _
Header:=xlGuess

rngRegular.Sort _
Key1:=Range("K" & FirstNonHighlightedCell), _
Order1:=xlAscending, _
Key2:=Range("A" & FirstNonHighlightedCell), _
Order2:=xlAscending, _
Header:=xlGuess

End Sub

 

[RyanH]

Yes, I noticed that too. Again, thanks for all your help! Hopefully one day
I will fully understand all this stuff. Do you know of any good text books

 

[Rookie_User]

How do you change this to work in 2007? I like the idea and have a similar
application but am having issues with it in 2007 Excel.