Sort range by color
 

Hey everyone!

I need to sort a range of cells (A6:H600) by interior color. I've
found some code that will sort the entire page, but I need to sort just
the range. There are only cells with a color index of 6 (yellow) and
all the rest are white. So all I need to do is have the yellow cells
show up at the top of the range, and then I'll delete the rest. This
is what I found that works for the whole worksheet:

Sub MySortingMacro()
'Based on Bernard Rey's routine
Const ISHEADER As Long = xlNo ' or xlYes or xlGuess
Dim cell As Range
Dim oldCalc As Long

With Application
oldCalc = .Calculation
.Calculation = xlManual
.ScreenUpdating = False
End With
With Selection(1) 'I changed this line to: With Range("D7")
With .EntireColumn
Columns(.Column).Insert ' Adding a temporary column
For Each cell In Intersect(.Cells,
ActiveSheet.UsedRange)
cell.Offset(0, -1).Value = cell.Interior.ColorIndex

Next cell
End With
.Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _
header:=ISHEADER
.Offset(0, -1).EntireColumn.Delete
End With
With Application
.Calculation = oldCalc
.ScreenUpdating = True
End With
End Sub

I've seen lots of other topics on this, but nothing that will work with
a specific range. Do I just need to change the line 'With Range("D7")'
to something else?

Sort range by color
 

You didn't like the first suggestion??

geetarista wrote:

Hey everyone!

I need to sort a range of cells (A6:H600) by interior color. I've
found some code that will sort the entire page, but I need to sort just
the range. There are only cells with a color index of 6 (yellow) and
all the rest are white. So all I need to do is have the yellow cells
show up at the top of the range, and then I'll delete the rest. This
is what I found that works for the whole worksheet:

Sub MySortingMacro()
'Based on Bernard Rey's routine
Const ISHEADER As Long = xlNo ' or xlYes or xlGuess
Dim cell As Range
Dim oldCalc As Long

With Application
oldCalc = .Calculation
.Calculation = xlManual
.ScreenUpdating = False
End With
With Selection(1) 'I changed this line to: With Range("D7")
With .EntireColumn
Columns(.Column).Insert ' Adding a temporary column
For Each cell In Intersect(.Cells,
ActiveSheet.UsedRange)
cell.Offset(0, -1).Value = cell.Interior.ColorIndex

Next cell
End With
.Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _
header:=ISHEADER
.Offset(0, -1).EntireColumn.Delete
End With
With Application
.Calculation = oldCalc
.ScreenUpdating = True
End With
End Sub

I've seen lots of other topics on this, but nothing that will work with
a specific range. Do I just need to change the line 'With Range("D7")'
to something else?

--

Dave Peterson

Sort range by color
 

I'm sorry about that--for some reason it reposted my original post and
didn't post my "thank you" reply.

Anyways, your suggestion actually worked perfectly for what I was
trying to do. Thank you!!!

On Sep 28, 3:57 pm, Dave Peterson wrote:
You didn't like the first suggestion??



geetarista wrote:

Hey everyone!

I need to sort a range of cells (A6:H600) by interior color. I've
found some code that will sort the entire page, but I need to sort just
the range. There are only cells with a color index of 6 (yellow) and
all the rest are white. So all I need to do is have the yellow cells
show up at the top of the range, and then I'll delete the rest. This
is what I found that works for the whole worksheet:

Sub MySortingMacro()
'Based on Bernard Rey's routine
Const ISHEADER As Long = xlNo ' or xlYes or xlGuess
Dim cell As Range
Dim oldCalc As Long

With Application
oldCalc = .Calculation
.Calculation = xlManual
.ScreenUpdating = False
End With
With Selection(1) 'I changed this line to: With Range("D7")
With .EntireColumn
Columns(.Column).Insert ' Adding a temporary column
For Each cell In Intersect(.Cells,
ActiveSheet.UsedRange)
cell.Offset(0, -1).Value = cell.Interior.ColorIndex

Next cell
End With
.Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _
header:=ISHEADER
.Offset(0, -1).EntireColumn.Delete
End With
With Application
.Calculation = oldCalc
.ScreenUpdating = True
End With
End Sub

I've seen lots of other topics on this, but nothing that will work with
a specific range. Do I just need to change the line 'With Range("D7")'
to something else?--

Dave Peterson