ExcelBanter - Sort by Cells Interior Color Then by Date

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.

"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"RyanH" wrote:

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?