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


Have a look at this page

Re: Sorting cells in a spreadsheet by colour



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

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)
.Cells(nC, 1).Select
End With
nC = nC + 1
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...


Josh Ord-Hume

Re: Sorting cells in a spreadsheet by colour


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


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,


Re: Sorting cells in a spreadsheet by colour


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:


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


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
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

I found the code here: