Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting by Color (Excel VB )

    Hello Forum,

    I am trying to assign a number to the colors on my spreadsheet but the VB code I found on the Forum is not working. Can someone tell me what's wrong with the code. I'm Stuck!

    Function ConvertColorToNumber(R As Range) As Integer
    ConvertColorToNumber = R.Interior.ColorIndex
    End Function

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting by Color (Excel VB )

    It works for me. What problem are you having? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Color (Excel VB )

    How did you get it to work.

    I went into Tools, Macro, create Macro and I have the following:
    Sub ConvertColorToNumber()
    Function ConvertColorToNumbers(R As Range) As Integer
    ConvertColorToNumber = R.Font.ColorIndex
    End Function
    End Sub

    But I get Compile Error -Expected End Sub <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting by Color (Excel VB )

    Ah, think I see what you don't like. No updates, right?
    Try this:
    <pre>Function ConvertColorToNumber(R As Range) As Integer
    Application.Volatile
    ConvertColorToNumber = R.Interior.ColorIndex
    End Function</pre>


    This causes the function to update its value whenever there is a calculation. It still will not update if you change a color, changing the color does not trigger a calculation. But, if you press F9 or add/change a cell entry, then the function value will be updated. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting by Color (Excel VB )

    Get rid of the lines starting with Sub and End Sub. Add the Application.Volatile
    --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Color (Excel VB )

    I'm still unable to run the macro. If I delete the Sub line then I remove the name of the macro so i can't see it on the macro menu bar. What I am trying to do is sort the excel spreadsheet by color. I would like to have all the yellows together, all the reds togother and so on.

    How did you get it to work?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting by Color (Excel VB )

    Once you have the function created, you don't need to run the macro. put the function in a column next to your cell of interest and enter it. For example:
    =ConvertColorToNumber(a1)
    In that cell the color index will appear. then sort on this column
    Steve

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting by Color (Excel VB )

    Here's a macro to sort by color. See attached worksheet to watch it run. HTH --Sam
    <pre>Option Explicit

    Sub SortByColor()
    Dim c As Range, upLt As Range, lowRt As Range
    ActiveCell.Next.EntireColumn.Insert
    Set upLt = ActiveCell.End(xlUp)
    Set lowRt = ActiveCell.End(xlDown).Next

    For Each c In Range(upLt, lowRt.Previous)
    c.Next.Value = c.Interior.ColorIndex
    Next c
    Range(upLt, lowRt).Next.Sort _
    key1:=upLt.Next
    lowRt.EntireColumn.Delete
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Color (Excel VB )

    Excellent!! Worked Perfectly!! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    Thank you so much for your patience and your help.

    I would be interested in learning how to do the function in excel, only if you feel like teaching.

    In any case, thanks and have a great weekend! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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