Sort Variable Range by color

[semsem]

Hi,
im trying to sort a variable range which will be different everytime i use the sheet, may be more columns and may be more rows, i have managed with vba to select the variable range everytime, but then when i try to sort it by cell color, i get error that VBA has a problem with (.apply)

here is the code i use to select and define the variable range and then try to sort it i get this error "the sort reference is not valid. make sure that it's within the data you want to sort, and the first sort by box isnt the same or blank"

   Range("O2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Dim LoadList As Range
    Set LoadList = Selection
    LoadList.Select
 
    ActiveWorkbook.Worksheets("Anycompare").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Anycompare").Sort.SortFields.Add Key:=Range( _
        "LoadList"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Anycompare").Sort.SortFields.Add Key:=Range( _
        "LoadList"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Anycompare").Sort
        .SetRange LoadList
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
       .Apply
    End With

 

[hiker95]

semsem,

See if this helps you get started in the right direction:

Sort by Color In Excel
This UDF (User Defined Function) will allow you to nominate a range of colored cells to be sorted by the color order chosen. In other words, Sort by color! This example code sorts by the cells Interior (Background) color. You can also sort by the cells Text color by Replacing Interior with Font.
http://www.ozgrid.com/VBA/Sort.htm

 

[semsem]

thanks hiker, but how is this code going to recognize which cells im talking about or which range? also can u pls explain that code a litte, what is it doing like that?

 

[hiker95]

semsem,

Sorry, I am not familiar with what you are trying to do.


Click on the Post Reply button, then just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.

 

[semsem]

hiker, im using an excel sheet in my work in which i add different amount of cells everytime i use this sheet, usually 2 columns and different amount of rows.

after processing this data, some of the cells become highlighted with different colors, what im trying to do is to create a macro to automatically sort those 2 columns to get a certain cell color in the first column (at top).

that's generally my idea, i was having trouble getting the macro to select the different amount of cells everytime and name it as a range and write a code to sort this range and get a specific color at the top.

in my first post there is the code that i reached.
when im trying to sort range like this (A2:A500) it is ok, but when i change the range to be a defined (named) range, vba gives me error and debug gets me to (.apply) and i dont really understand what am i doing wrong.

 

[semsem]

any help?