excel colour sort

[faxof]

hello

if i have a column of data, some cells are red, some are green. is there a way i can sort them by colour?

quizzed

fax

 

[SkipVought]

fax,

Not without VBA code.

What column has the shaded interior?


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[faxof]

it's colun A (the only column). it has people's names in their team colours. obviously i can sort it alphabetically but not by team colour.

 

[SkipVought]

Sub SortByInteriorColor()
   With ActiveSheet.UsedRange
      For Each s In Range(Cells(.Row, 1), Cells(.Row + .Rows.Count - 1, 1))
         With s
            Cells(.Row, "B").Value = .Interior.ColorIndex
         End With
      Next
   End With
   With ActiveSheet.UsedRange
      .Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo _
         , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
   End With
End Sub

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[AlanKW]

I had to do this a few months back and of course can't find it now, but I wrote a macro to place a value (Selection.Interior.ColorIndex) representing the color in a temporary column.

The phrase "sorting by color" is a little dubious. ie what is greater; Blue or Red. So by putting a the Color index next to the team, you can then group by Color (index)

 

[faxof]

that's good, thanks very much. however . . .

if i wanted to do it without code would something like this work:
put the data in A and formula in B, the pseudo formula in B1 would be something like =IF texcolour(A1)=red,"red","green"
then copy down
then sort on column B

could that work?

fax

 

[SkipVought]

Function WhatInteriorColor(rng)
   WhatInteriorColor = rng.Interior.ColorIndex
End Function

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[AlanKW]

is it Background Shading or Font color? To my knowledge there is no excel function that reads either. That is why the answer lies in VB.

 

[SkipVought]

If the FONT is shaded

Function WhatFontColor(rng)
   WhatFontColor = rng.Font.ColorIndex
End Function

BTW,

The function must reside in a Module and NOT a Sheet OBject.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[KenWright]

IIRC your cells can't have been coloured via Conditional formatting if the VBA code is to work, you need to have manually coloured them else the functions will not recognise the colour.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GeekGirlau]

Actually another way to do it is have a column specifically for the Team.

Create a column for Team, and use conditional formatting to set the colour according to the Team selected. The column could be as wide or narrow as you like, depending on whether you want to be able to read the text. If you don't want to read the text, have the conditional formatting set the text colour the same colour as the cell background.

Now you can sort your data by Team, then Name - no code required

 

[gizmo1973]

There is an application on the market that does this.
I bought it for about GBP30 it will has many invaluable units such as filters, roll ups etc.
It's well worth checking out as I wouldn't be without it.

http://www.digdb.com/

Regards, Phil.
C.E.O. Bodgeit, Leggit & Scarper. International.
"Stuffing things up completely since 1973"