Sorting by color

  • Dear all


    I have a huge list of items with their properties entered through 4 columns. Some of the rows are filled with a color for immediate identification. Now I would like to sort all these coloured rows, so that they appear in one stretch. Please help me.

  • Re: Sorting by color


    For Eg.


    Row 1 - Item A
    Row 2 - Item B (Coloured row)
    Row 3 - Item c
    Row 4 - Item D (Coloured Row)
    Row 5 - Item E
    Row 6 - Item F (Coloured Row)


    Out of the above, Row No 2,4 and 6 are coloured rows. Now I would like to sort the above list in such a way that the coloured rows should appear one by one and then others.


    regards

  • Re: Sorting by color


    Hi,


    You can check Dave's tutorial or in this forum. This question been already answered.


    This what I use(though a raw one):
    [vba]
    Function Getcolor(Rn As Range) As Integer
    Dim d As Long
    d = Rn.Interior.ColorIndex
    Getcolor = d
    End Function
    [/vba]


    Place this UDF in your workbook module and in your worksheet do the following:
    1. Insert a column just to the left the data
    2. Press Shift+F3 and select UDF(User Defined Functions)..here you can find Getcolor funtion.
    3. Select this function and link to the data and drag it through till the end. This function would have returned the color index, through which you can sort your data. After you are through with this you can delete the coloumn which you have inserted.


    This function doesn't hold good to sort for font colors.


    HTH.

  • Re: Sorting by color


    Assuming your data is A1:B10, where row 1 contains column headings. You could run a procedure such as this, which inserts a temporary column C containing the interior colorindex of column B cells....


    The procedure then sorts by the colorindex and removes the temp column


    You will need to adjust to suit your ranges.


    [vba]
    Dim rng As Range
    For Each rng In Range("B2:B10")
    rng.Offset(0, 1).Value = rng.Interior.ColorIndex
    Next rng
    With Range("A1").CurrentRegion
    .Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    .Columns(3).Clear
    End With
    [/vba]



    Edit: Good call Pradeep... I hadn't seen Dave's tutorial.... looks like pretty much the same idea though ;)

  • Re: Sorting by color


    Dear Pradeep


    I made it in my module but the result for =getcolor(range) is #VALUE!. I selected the whole range of data includeing column headings. Please guide me.


    Regards

  • Re: Sorting by color


    Quote from aurobless

    Dear Pradeep


    I selected the whole range of data includeing column headings. Please guide me.


    Regards


    No, not the whole range... just a single cell & copy down formula


    i.e.
    =Getcolor(A1)

  • Re: Sorting by color


    Dear Pradeep & Mr. Willr


    I have taken up Mr. Pradeep's solution and it works fine. But his code does not give result when referred to a cell which has a conditional formatting...


    Can you help me.


    Warm Regards

  • Re: Sorting by color


    Dear Pradeep / guys



    The above code does not respond to cells having conditional formatting. May anyone please help ?


    Warm Regards

  • Re: Sorting by color


    Hi Srinivasan,


    To sort the data having cells formating, you need to supply one more argument i.e TRUE or FALSE. Have one more column with the formula which you have mentioned in the conditional formating. This cell will return either true or False. So you need to select two cells in the CCI function(one is the usual cell which is the range and another cell is the new columns which you have inserted which tests the condition of the conditional formating.


    Though this is not an elegant way, this works if you are working on only the cells with the CD.


    [vba]
    Function CCI(rng As Range, test As Boolean)
    If test = True Then
    CCI = rng.FormatConditions(1).Interior.ColorIndex
    Else
    CCI = rng.Interior.ColorIndex
    End If
    End Function
    [/vba]


    HTH.

  • Re: Sorting by color


    Pradeep


    Why do you need a UDF for this?


    Can't you just create a worksheet function that returns a value based on the condition(s) used in the conditional formatting?

  • Re: Sorting by color


    Hi Norie,


    You are correct.


    The second column which I asked Srinvasan to insert to test the conditional formating condition will be alone enough to sort the data. However this is applicable only if there are only one condition attached to the CD.


    As I already said the solution which I suggested may not be an elegant one and there may be better way of handling this.


    Srinivasan,


    You can use the newly inserted column which tests the condition of the CD to sort the data, if you have only one condition attached to your CD.


    If you are using the code, You need not delete the earlier UDF, just add this new UDF to your module and start using the new UDF.


    HTH.


    thanx

  • Re: Sorting by color


    Well Norie,


    Sorry If in my earlier post if I have given an impression that it is not possible.


    It was a simple formula I agree. Prior to this, it was an UDF used to sort the data based on color index, and I went in those lines to solve this problem as well.


    BTW is there a way to test if a range is applied some CD.


    And given all these things let Srinivasan use the method which he feels comfortable..;


    Thanx

  • Re: Sorting by color


    Dear Pradeep


    thanks for the code. I have copied the same. When I executed =cci(a1) it returns to #value. Am I doing some mistake?


    regards

  • Re: Sorting by color


    Hi Srinivasan,


    As explained in my earlier posts, you need to supply another parameter True or False(which is nothing but the outcome of your formula in the CD.


    =CCI(Cell Reference 1, Cell Reference 2)


    The first cell reference is your cell with CD. and the second one is which has the formula of the CD in it which returns either TRUE/FLASE.


    Instead of using the UDF you can insert another column with the formula in your CD entered into such that it will return either TRUE or FALSE and you can sort on this column.


    HTH.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!