sorting excel with color tags (1 Viewer)

[zelld]

for the longest time, i have been wondering if there is an answer to this question: is there a way to sort data in excel by color tags. thanks. i would appreciate any answer to this more or less 5-year old question.

 

[chergh]

Take a look at http://www.cpearson.com/excel/colors.aspx

 

[Brianwarnock]

i would appreciate any answer to this more or less 5-year old question

I posted the code below on this forum way back, its amazing how useful searching can be.

Brian

Sub sortcolour()
'Brian Warnock December 2005
'Sort rows by colour index

Dim lnglastrow As Long
Dim lnglastcol As Long
Dim lnglastcol2 As Long

lnglastrow = ActiveSheet.UsedRange.Rows.Count
lnglastcol = ActiveSheet.UsedRange.Columns.Count
lnglastcol2 = lnglastcol + 1


For Each c In Range(Cells(1, lnglastcol), Cells(lnglastrow, lnglastcol))
    c.Offset(0, 1).Value = c.Interior.ColorIndex
    Next c
    
ActiveSheet.Range(Cells(1, 1), Cells(lnglastrow, lnglastcol2)).Sort _
key1:=ActiveSheet.Cells(1, lnglastcol2), order1:=xlDescending

ActiveSheet.Cells(1, lnglastcol2).EntireColumn.Delete

End Sub

 

[zelld]

how exactly would one go about using this code? call me a noob or an ignoramous (which i am, actually), but i have no idea how to apply this knowledge you handed out? i apologize for the idiocy, but i am smiling in my chair knowing that this can actually be done

 

[Brianwarnock]

Sorry for the delay been hospital visiting.

In your spreadsheet use Alt+F11 to open the VBA editor, from the menu select Insert then module. select the module by clicking on it and then paste the code into the module,
From tools you will now be able to run the macro.

Brian

 

[zelld]

(insert sheepish grin smiley here)

brian, you have just made my excel reports 200% easier to make. you can't believe how i keep on imposing on my people to create a separate column and assign tags so that i can sort stuff easily, and yet the insist on using highlights - now i don't have to worry about that. for the life of me, i can't imagine why i have not searched for this answer all these years i've been using excel. i owe you a cold one

 

[Brianwarnock]

Glad to have been of help, that link posted by chergh makes for interesting reading if you have the time to study it.

Brian