• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to custom sort data based on cell interior color

ThrottleWorks

Excel Ninja
Hi,

I have a colour range in a worksheet.

I need to sort this range based on the cell interior colour.

All the colours which are not VBYELLOW will remain at top, followed by no colour cells, at the end we will have VBYELLOW cells.

Please see attached file for more details. Results should overwrite original content, data given in two different sheets for reference purpose.

Can anyone please help me in this.
 

Attachments

  • Sort by cell color.xlsx
    10.7 KB · Views: 5
Hi ,

Try this :
Code:
Public Sub CustomSortByColour()
'          I have assumed that the column to the right of the data range is an unused column
'          If this is not so , then the variable newcol will have to be set accordingly

           Dim inputdatarange As Range
          
           Set inputdatarange = ThisWorkbook.Worksheets("Input").Range("A1:O19") ' alter to suit
            Application.ScreenUpdating = False
          
           With inputdatarange
                newcol = .Columns.Count + 1
                .Cells(1, newcol).Value = "TempCol"
                For i = 2 To .Rows.Count
                    If .Cells(i, 1).Interior.Color = vbWhite Then
                       .Cells(i, newcol).Value = 2
                    ElseIf .Cells(i, 1).Interior.Color = vbYellow Then
                       .Cells(i, newcol).Value = 1
                    Else
                       .Cells(i, newcol).Value = 3
                    End If
                Next
          
                Set inputdatarange = .Resize(.Rows.Count, newcol)
            End With
          
            With inputdatarange
                 .Sort .Columns(newcol), xlDescending, , , , , , xlYes, , , xlSortColumns, xlPinYin, xlSortNormal
          
                 .Columns(newcol).ClearContents
            End With
            Application.ScreenUpdating = True
End Sub
Narayan
 
Hi @NARAYANK991 sir, tried your code. Thanks a lot.

I might have failed to describe my problem in correct way.
I need to sort all colour also. if you could please refer attached file in original post.

Please go to sheet2, row 2:3 are of same colour, row 4:7 are of same colour, same goes for row 9:10.

Sorry for churn out, could you please help if you get time.
 
Back
Top