Sort Range by color

[nandobarreto]

Hi,
I have a Table where I color some cells and sometimes I need to sort them by color.
To do so, I go on the sort menu.
I would like to do this using a VBA macro

I "wrote" this, but isnt working,

Sub Macro2()
'
   Dim rng As String
    rng = Selection.Address

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:= _
        Range(rng), SortOn:=xlSortOnCellColor, Order:=xlDescending, _
        DataOption:=xlSortNormal
    
With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(rng)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

    End With 
End Sub

I tried too:

Dim rng As Range
Set rng = Range(Selection.Address)

No lucky!
Any help?

Thanks

 

[Fluff]

In what way isn't it working?

 

[Yongle]

Have a look at this

Excel Sort by Color with VBA

Previously in Podcast 2093, I showed a simple VBA sort that works if you are not sorting by color. Today, Neeta asks for the VBA to sort Excel data by color.

www.mrexcel.com

 

[nandobarreto]

In what way isn't it working?

Thats a good question, i forgot to mention!

When I select only 1 column it does what should
When I select 2 or more, gives me an error (i'll try translate):

The classification reference is not valid. Check if the reference is among the data you want to sort and that the first box to sort by is not the same or is blank.

Have a look at this

Excel Sort by Color with VBA

Previously in Podcast 2093, I showed a simple VBA sort that works if you are not sorting by color. Today, Neeta asks for the VBA to sort Excel data by color.

www.mrexcel.com

Thanks for your reply, but this classifies columns, I want to classify only SELECTED ranges on a sheet.
I looked this before post.

 

[Fluff]

How about

Sub nandobarreto()
   Dim i As Long
   
   With ActiveSheet.Sort
      .SortFields.Clear
      For i = 1 To Selection.Columns.count
         .SortFields.Add Selection.Columns(i), xlSortOnCellColor, xlDescending, xlSortNormal
      Next i
      .SetRange Selection
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

 

[nandobarreto]

How about

Sub nandobarreto()
   Dim i As Long
  
   With ActiveSheet.Sort
      .SortFields.Clear
      For i = 1 To Selection.Columns.count
         .SortFields.Add Selection.Columns(i), xlSortOnCellColor, xlDescending, xlSortNormal
      Next i
      .SetRange Selection
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

Wow! worked perfectly!
1 more question:

Is it possible to first put colors on the top, second no colors and 3rd blank cells?

BTW, thank you so much!

 

[Fluff]

How about

Sub nandobarreto()
   Dim i As Long
   
   With ActiveSheet.Sort
      .SortFields.Clear
      For i = 1 To Selection.Columns.count
         .SortFields.Add Selection.Columns(i), xlSortOnCellColor, xlDescending, xlSortNormal
      Next i
      For i = 1 To Selection.Columns.count
         .SortFields.Add Selection.Columns(i), xlSortOnValues, xlAscending, xlSortNormal
      Next i
      .SetRange Selection
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

 

[nandobarreto]

How about

Perfect!
Thank you!

 

[nandobarreto]

Actually, if the first cell is colorless, it'll keep as first, and sort only after the second line.
And if I select the whole line it'll return an error:

application definition or object definition error

The first problem I tryed to change i=0 but wont work.

 

[Fluff]

Change .Header = xlYes to .Header = xlNo