Sorting by cell color

[logandiana]

I used the macro recorder to get what I wanted, now trying to shorten and simplify.


I want to sort by all my red cells at the top, then by number descending.

Can't figure out the syntax of the code.
I've never used VBA to sort by color before.

Here's what I've got.

Sub sortred()
LR1 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A3:M" & LR1).Sort.SortOnValue.Color = RGB(255, 80 , 80), key1:=Range("C3:C" & LR1),order1:=xlAscending, key2:=Range("D3:D" & LR1), order2:=xlDescending, Header:=xlYes
End Sub

 

[cerfani]

Re: help with SYNTAX, sorting by cell color

You are mixing up syntax of calling a function (Sort) and setting a value (on a property).

Setting color property example: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/sortfield-sortonvalue-property-excel (scroll down a little)

Range("A3:M" & LR1).Sort.SortOnValue.Color = RGB(255, 80 , 80)

Calling Range.Sort example: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-sort-method-excel (might need parentheses around parameters, i forget what vba compiler requires)

Range("A3:M" & LR1).Sort key1:=Range("C3:C" & LR1),order1:=xlAscending, key2:=Range("D3:D" & LR1), order2:=xlDescending, Header:=xlYes

 

[logandiana]

Re: help with SYNTAX, sorting by cell color

You are mixing up syntax of calling a function (Sort) and setting a value (on a property).

Range("A3:M" & LR1).Sort.SortOnValue.Color = RGB(255, 80 , 80)

Not working, getting error Unable to get the Sort property of the Range class

Here's the code from the macro recorder that I am trying to simplfy

ActiveWorkbook.Worksheets("Monthly").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Monthly").Sort.SortFields.Add(Range("C4:C3030"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 80 _
        , 80)
ActiveWorkbook.Worksheets("Monthly").Sort.SortFields.Add Key:=Range( _
        "D4:D3030"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Monthly").Sort
        .SetRange Range("A3:M3030")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[cerfani]

Re: help with SYNTAX, sorting by cell color

if you are just trying to modify some recorded macro to have the range extend to the bottom of the table then you simply need to set your LR1 value, like you did and replace all instances of...

3030"

with

" & LR1

dont touch anything else.

Also if you are ever getting help with a problem, never just say "it doesn't work." You have to describe the symptoms that tell you that it doesn't work as the remedy to fix the issue is dictated by the complications you would describe. Like going to a doctor

 

[logandiana]

Re: help with SYNTAX, sorting by cell color

if you are just trying to modify some recorded macro to have the range extend to the bottom of the table then you simply need to set your LR1 value, like you did and replace all instances of...

3030"

with

" & LR1

dont touch anything else.

Also if you are ever getting help with a problem, never just say "it doesn't work." You have to describe the symptoms that tell you that it doesn't work as the remedy to fix the issue is dictated by the complications you would describe. Like going to a doctor

That's not all I am trying to do though.

I do want to replace the sort with a dynamic range. But I also want to simplify and declutter what the macro recorder gave me.

Normally I have been able to reduce all the 'activeworkbook' stuff down to one single line of code. But that's always been with sorting with numbers, using key1, order1, key2, order2, etc.
I've never had to first sort the cells based off of cell fill color first.
I tried to write it in one line, but I can't get it to work.
From your first reply and links I took that to mean that it could be shortened, but that they needed to come in two lines of code, one right after the other.
I tried it this way, but I get an error on the first line.
I hope I have explained this well.

 

[shg]

The Range.Sort method is from earlier versions of Excel (3003 and earlier) that did not support support sorting by color.

Sub LD()
  Dim wks           As Worksheet

  Set wks = Worksheets("Monthly")

  With wks
    .Sort.SortFields.Clear
    .Sort.SortFields.Add(Key:=.Range("C4"), _
                         SortOn:=xlSortOnCellColor, _
                         Order:=xlAscending).SortOnValue.Color = RGB(255, 80, 80)
    .Sort.SortFields.Add Key:=.Range("C4"), _
                         SortOn:=xlSortOnValues, Order:=xlDescending
    .Sort.SetRange .Range("C4", .Cells(.Rows.Count, "C"))
    .Sort.Header = xlYes
    .Sort.Orientation = xlTopToBottom
    .Sort.Apply
  End With
End Sub

 

[logandiana]

Perfect, thank you!

The Range.Sort method is from earlier versions of Excel (3003 and earlier) that did not support support sorting by color.

Sub LD()
  Dim wks           As Worksheet

  Set wks = Worksheets("Monthly")

  With wks
    .Sort.SortFields.Clear
    .Sort.SortFields.Add(Key:=.Range("C4"), _
                         SortOn:=xlSortOnCellColor, _
                         Order:=xlAscending).SortOnValue.Color = RGB(255, 80, 80)
    .Sort.SortFields.Add Key:=.Range("C4"), _
                         SortOn:=xlSortOnValues, Order:=xlDescending
    .Sort.SetRange .Range("C4", .Cells(.Rows.Count, "C"))
    .Sort.Header = xlYes
    .Sort.Orientation = xlTopToBottom
    .Sort.Apply
  End With
End Sub

 

[Rick Rothstein]

The Range.Sort method is from earlier versions of Excel (3003 and earlier) that did not support support sorting by color.

So, you are a visitor from the far future, eh? Please tell us what things will be like then.