Josh Ord-Hume


My apologies if I am asking this question in completely the wrong forum.
I am trying to create a VBA macro that will order data in an Excel spreadsheet according to colour (so that like colours are all grouped together).
I wonder if somebody would be kind enough to spend some time explaining how to do this to a more or less complete VBA novice!

Many thanks...

Josh Ord-Hume





Re: Sorting cells in a spreadsheet by colour

Andy Pope


Hi,

Have a look at this page
http://www.xldynamic.com/source/xld.ColourCounter.html#sorting






Re: Sorting cells in a spreadsheet by colour

bi-lya

Hi!

This code transfers a range of 3 cells from the sheet 1 to the sheet 2.
Choose any color cell in the column 1 of the sheet 1 and run the code

Code Snippet

Public nC As Integer

Public Sub TransferColor()

Application.ScreenUpdating = False

If nC = 0 Then nC = 1

un = ActiveCell.Interior.ColorIndex

If un < 0 Then Exit Sub

Application.FindFormat.Interior.ColorIndex = un

Do
On Error Resume Next
nr = Range("A:A").Find("", Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False, , True).Row
If Err.Number = 0 Then
Range(Cells(nr, 1), Cells(nr, 3)).Cut
With Worksheets(2)
.Activate
.Cells(nC, 1).Select
.Paste
End With
nC = nC + 1
Worksheets(1).Activate
End If
Loop Until Err.Number <> 0
On Error GoTo 0

Application.ScreenUpdating = True

End Sub






Re: Sorting cells in a spreadsheet by colour

Josh Ord-Hume

Dear bi-lya...

I have incoorporated this code into my spreadsheet, but it does not appear to have any effect whatsoever. It doesn't even generate an error! What could I be doing wrong Is it enough to simply paste this code into "Book 1 - Sheet1" in the Visual Basic editor
Please forgive my ignorance - I have absolutely no programming experience...

Regards,

Josh Ord-Hume





Re: Sorting cells in a spreadsheet by colour

magicalclick

Hi Josh, how much do you know about VBA Like record macro and assign values to a cell The short answer is just output the color code into a column and sort the column.




Re: Sorting cells in a spreadsheet by colour

bi-lya

Hi,
Josh Ord-Hume, in order to this code begins to work you need: or start it to use other "macro", or to use some event of the sheet (Worksheet_SelectionChange or other), or to use a button.
For example:
- paint several cells in the column "A" in different color
- paste the following handler of the event Worksheet_SelectionChange into the module of the sheet 1:

Code Snippet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 1 Then TransferColor
End Sub

Now, when you click any colour cell in the column "A" of the sheet 1, all cells the same colour will transfer in the sheet 2




Re: Sorting cells in a spreadsheet by colour

Josh Ord-Hume

Dear Magicalclick...
I can see the logic in your suggestion. How might I 'output the color code'

Many thanks,

Josh






Re: Sorting cells in a spreadsheet by colour

crainsworth

Ok, here is the easy way:

Create a function that returns a value for a cell's color by copying this code into the VB editor. Remember that with this approach you are not creating an executable macro, just a function that can be used like any other excel function (left, right, mid, vlookup, sum). So, all you need to do is insert a new column and enter this formula:

=CellColorIndex(B1)

Now you can sort the record to group the colors together.

Code:

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer

Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

I found the code here:

http://www.cpearson.com/excel/colors.htm