Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Sorting items by color

    Hi guys,

    Back from holiday and ready to improve my document some more. Some things are going on the back burner for now, but have other ideas that should be faster and easier to implement.
    I was wondering if someone can help me how I should go about sorting the content of a cell range by color and then within each color alphabetically?

    The column "F" can have items with text color of Black, Blue, Green, Orange, Purple and Red... When typing them in, the color is assigned automatically already, but the order can be random. I am guessing there should be a color sorting first, then an alphabetical sort one by one within each color. Also the preferred color order is Red, Blue, Orange, Green, Black and Purple.

    Thank you for looking,
    Ferenc

  2. #2
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I found something that does most of the job except for the color sorting in the proper order...
    Any thoughts on how I would modify this to result in: Red, Blue, Orange, Green, Purple & Black?

    Code:
    Sub Sort_by_FontColor()    
        Application.ScreenUpdating = 0
        [a1:w99].Sort key1:=[a1], Header:=xlYes, Order1:=xlAscending
        Columns("b").Insert
        For i = 2 To Range("a65536").End(xlUp).Row
            Cells(1, 2) = "Color Index"
            Cells(i, 2) = Cells(i, 1).Font.ColorIndex
        Next
        Range("a2:" & Chr(64 + Cells(1, 1).End(xlToRight).Column) & Range("A1").End(xlDown).Row).Sort Range("b2"), xlAscending
        Columns("b").Delete
    
    
    End Sub

  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    You could allocate a value based on the cell colour and then sort by that. I've done it with VB standard colours, but you could use the RGB values of your particular colour to allocate them, e.g.
    Code:
    Sub Sort_by_CellColor()
        Dim i As Integer
        Application.ScreenUpdating = 0
        [a1:w99].Sort key1:=[a1], Header:=xlYes, Order1:=xlAscending
        Columns("b").Insert
        For i = 2 To Range("a65536").End(xlUp).Row
            Cells(1, 2) = "Colour Code"
                If Cells(i, 1).Interior.Color = vbRed Then
                    Cells(i, 2) = 1
                    ElseIf Cells(i, 1).Interior.Color = vbBlue Then Cells(i, 2) = 2
                    ElseIf Cells(i, 1).Interior.Color = vbYellow Then Cells(i, 2) = 3
                    ElseIf Cells(i, 1).Interior.Color = vbGreen Then Cells(i, 2) = 4
                    ElseIf Cells(i, 1).Interior.Color = vbBlack Then Cells(i, 2) = 5
                    ElseIf Cells(i, 1).Interior.Color = vbMagenta Then Cells(i, 2) = 6
                    Else: Cells(i, 2) = 7 'to capture non-standard colours
            End If
        Next
        Range("a2:" & Chr(64 + Cells(1, 1).End(xlToRight).Column) & Range("A1").End(xlDown).Row).Sort Range("b2"), xlAscending
        Columns("b").Delete
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Changed "Interior" to "Font" and replaced vbColor with the numeric value of each and it is working like a charm

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •