My apologies if I am asking this question in completely the wrong forum. Many thanks...
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!
Josh Ord-Hume
Have a look at this page
http://www.xldynamic.com/source/xld.ColourCounter.html#sorting
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
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
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
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:
Dear Magicalclick...
I can see the logic in your suggestion. How might I 'output the color code'?Many thanks,
Josh
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 End Function
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If
I found the code here:
http://www.cpearson.com/excel/colors.htm
