Results 1 to 14 of 14

Thread: sort by color

  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: sort by color

    Hey guys!!! so I'm on my last leg of my project and it only took me 4 questions. Not bad for a first timer. I think....

    Anyway so now I have a spreadsheet and the cells in colomn B are all different colors. lets just say red, blue, green, and purple. so now I need to sort by color. now I only change one row at a time so; I do some stuff It causes cell B to change color, lets say purple, (got that already). Now I want to move it up or down (99% of the time down) in the sheet untill it is in that color, purple for this example, and then sort all the purple cells in alaphebetical order according the values of colomn B.

    By the way thanks to everyone that helped me out

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Here is a detailed explanation with VBA code to do exactly that.

    http://www.cpearson.com/excel/SortByColor.htm
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jul 2004
    Posts
    34

    hmmmm........

    yeah i really don't like that method..... I don't want to make an extra column, it sorts by colors but then doesnt sort the colors themselves I already have my color order it's 36, 35, 38, 8, 39, 4, 33, 3, 43, and xlnone

    not to mention there is a lot of stuff you have to do manually in there and the point of the macro is to automate everything. Since I am only changing one line at a time it would be easier to cut and insert cells like I am currently doing than using this method. Thanks for the help though. Any more ideas would still be appreciated.

    Is there a move down till command? or something like that? I think with something like that I could get on my way

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Will something like this help?

    Code:
    Dim R As Range 
    For Each R In Range("A1:A" & Cells(65536, 1).End(xlUp).Row) 
        R.Offset(0, 3).Value = R.Font.ColorIndex 
    Next R
    Puts colorindex in column D, based on column A. Adjust as needed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Or perhaps this approach using a UDF and sorting based on that:

    Code:
    Function IntColor(myrange As Range) 
    IntColor = myrange.Interior.ColorIndex 
    End Function
    To sort, insert new column, use the function:

    =IntColor(a3)

    which gives the ID of the color. Sort the table by this column.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091

    Talking

    Or look over this at OzGrid...

    http://www.ozgrid.com/VBA/Sort.htm

    There should be something that appeals to you... LOL
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jul 2004
    Posts
    34

    Lightbulb almost thanks

    Thanks shades that gave me almost exactly what I want. From what you have given me I came up with this. Since this is my first time I don't know the syntax for sure look it over and let me know please. Thanks.

    I highlighted the parts I think are problems in Bold, colomn B is the colomn with color I am sorting by, and I moved it to the top of the sheet frist to be sure that it always comes in contact with the color I am looking for (just in case the color was up from the origional location)

    dim i as integer
    i=4

    ' cut entire row
    activecell.row.select
    selection.cut

    ' insert row at top of page
    rows("3:3").inseft shift:=xldown

    ' my do condition
    Do until range("B" & activecell.row).interior colorindex = range("B" & _ activecell.row+1).interior.colorindex

    ' Move row down
    selection.cut
    rows("i:i").insert shift:=xldown
    i = i + 1
    Loop



    One last thing. How do I get the code box to show when I am trying to post? code looks so much better in a code box.....

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by VBALordCorp

    One last thing. How do I get the code box to show when I am trying to post? code looks so much better in a code box.....
    use this without the spaces:

    [ c o d e ]

    put code here

    [ / c o d e ]
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    A couple of quick pointers for you that ive noticed here

    rows("i:i").insert shift:=xldown

    this wont work as it will try to find row i rather than the row which is determined by variable i
    when you cut and insert then your activecell won't actually change

    activecell.row.select this doesn't work you need to use the comand
    Entirerow instead if row here as row just produces a number and entirerow produces a range

    ive looked through your code and sorted it to this
    Code:
    Sub test()
        Dim i As Integer
        i = 4
    
        ' cut entire row
        ActiveCell.EntireRow.Cut
    
        ' insert row at top of page
        Range("A3").EntireRow.Insert shift:=xlDown
    
        ' my do condition
        Range("B4").Select
        Do Until Range("B" & i).Interior.ColorIndex = Range("B" & i - 1).Interior.ColorIndex
    
            ' Move row down
            Range("A" & i - 1).EntireRow.Cut
            Range("A" & i + 1).EntireRow.Insert shift:=xlDown
            i = i + 1
        Loop
    End Sub
    i think this is what you were trying to achieve

    to get the code box all you have to do is type [ code ]
    insert your code then
    [ /code ]
    do this without the spaces

    David

  10. #10
    Join Date
    Jul 2004
    Posts
    34

    got what i asked for

    Thanks guys that did exactly what I wanted it to do. only it takes forever to move it down. If anyone has any better idea's I'm still open to suggestions

  11. #11
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    hi this is how i would do this

    Code:
    Sub SortColours()
        Dim R As Range
        Dim Lastrow As Long
        Dim ColorNumber As Variant
        Dim clr As Variant
        
        Range("C1").EntireColumn.Insert
        
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Range("C1").Formula = "Colorindex"
    
        'code from shades (dbforums) altered
            
        For Each R In Range("B2:B" & Lastrow)
            R.Offset(0, 1).Value = R.Interior.ColorIndex
        Next R
        
        'array set up in reverse color number
        ColourNumber = Array(-4142, 43, 3, 33, 4, 39, 8, 38, 35, 36)
        
        'blank each number then sort
        With Range("C2:C" & Lastrow)
            For Each clr In ColourNumber
                .Replace clr, ""
                Range(Cells(1, 1), Cells(Lastrow, Range("IV1").End(xlToLeft).Column)) _
                    .Sort Range("C2"), header:=xlYes
            Next clr
        End With
        
        'delete the column with the colorindex numbers
        Range("C1").EntireColumn.Delete
    End Sub
    post back iof youve got any questions

  12. #12
    Join Date
    Jul 2004
    Posts
    34

    ...???...

    Well that code did move it down and a lot faster too but consiquently it made my color order all messed up. This looks like it has a huge potential of helping me as long as it didnt mess my colors up. What happened?

  13. #13
    Join Date
    Jul 2004
    Posts
    34

    new color scheme

    ok so it changed my color scheme to 35,6,36,39,8,38,4,3,33,43 not a clue why I'm new at this and dont fully understand each step of the code... Sorry.

  14. #14
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    sorry ive had a quick look at this and noticed i missed something out,
    It was working fine for me before but that was with previous options set,

    the way to sort this is in the line

    .Replace clr, ""

    Change this to

    .Replace clr, "", xlWhole

    Ill tell you how the code works
    it inserts a row then puts the value of the colorindex of each cell in row b into that row
    it then replaces the number with blanks in reverse order of your colour order
    sorting each time as excel sorts blanks to the end
    the mistake came when replacing the numbers it was replacing any part of the number with blanks so i.e. if it tried to clear 3, 34 would be changed to 4 etc.

    the xlWhole part makes sure that the code was looking at only the whole cell so has sorted this problem

    HTH

    David

Posting Permissions

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