Sorting By Color in Excel (2003) 3

[wyvern621]

Murnin All...

I was told to come here, so here I am...

I have a spreadsheet with a few thousands rows on it and some of the rows are highlighted yellow. Is there a way to sort this spreadsheet by color?

Thanks in advance...

ChiTownDiva

 

[SkipVought]

Hi,

Here's a function that returns the ColorIndex of the Interior of a cell. Paste it into a MODULE and then use it, as you would any spreadsheet function in your sheet, in a new column. Then sort on the values in the new column...

function WhatInteriorColorIndex(rng as range)
   WhatInteriorColorIndex = rng.interior.colorindex
end function

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

but be aware that the sort order will not necessarily be the order that you want - it will be determined by the colorindex for the colours and not what it means to you the user

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maclav]

Hey. how good are you with vba?
If I was given this task I would set up a double while loop
the first would go through the possible colours and inside that one it would go through each row.
With that number of rows i doubt it would be quick.

would go a bit like this

dim top as integer
dim bottom as integer
dim num_colours as integer
dim current_row as integer
dim current_col as integer

num_colours=4 'if there are more than 4 colour change this
current_row=1
current_col=1
top=1

bottom='your prefered method of finding the last row number.
while current_col<=num_colours
while current_row<=bottom

'in here do the code that checks the colour and if it matches swaps that row with the "top" row and increases top by 1. This will eventually give you what you want


current_row=current_row+1
wend
current_col=current_col+1
wend

 

[AnotherHiggins]

(Note to readers: This thread is a continuation of thread68-1421838)

So no logic behind why the cells are colored he way they are, eh? Bummer.

Here's a snazzy way to return values in the order in which you want your sheet sorted.

If you just want the colors grouped and order isn't very important, Skip's suggestion is way simpler.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mscallisto]

' you can sort colors in any order you wish if you simply 
' replace the colorindex value with a valus that corresponds to ' whatever sort order you want, e.g.

' For(Blue ColorIndex 5)to sort before (Red ColorIndex 3)
' simply replace the red index (3) with a (6) one greater than ' blue (5)

 

[wyvern621]

Hi Skip, Hi John...

I'm Excel challenged...at this morning I am...

I tried both pieces of code in a module and used it as a function and got the dreaded #NAME? error.

Skip and John, I copied and pasted your codes in a module on General Declarations, then used it as a function in column X:

=ColorRank($a$2:$a$3, b2) or

=WhatInteriorColorIndex($B$2:$B$25910)

What am I not doing?

Thanks in advance.

ChiTownDiva

 

[AnotherHiggins]

Just to be sure: You pasted the code into a Module, right? That is to say, [!]NOT[/!] a Sheet or ThisWorkbook.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Skips function needs to test ONE CELL ONLY:

=WhatInteriorColorIndex(B2)

where B2 is a coloured cell

You would need to then copy down and sort on the numbers generated



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

For instance in C2 enter ths formula
[tt]
C2: =WhatInteriorColorIndex(B2)
[/tt]
then COPY, select C2:C25910 and PASTE.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[wyvern621]

Hi John and Geoff...

I went to:

Tools > Macro > Visual Basic Editor

and pasted in the code on [Sheet3(Code)], saved it and then used it as a function on sheet3, column x (which is where I need it).

ChiTownDiva

 

[xlbo]

You need to paste it into a standard module NOT a sheet module

You also need to call the function correctly as I have specified

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wyvern621]

Hey Skip...

Did that and I still get the #Name? error...

ChiTownDiva

 

[AnotherHiggins]

[!]NOT[/!] a Sheet or ThisWorkbook

Right click on the Sheet or ThisWorkbook and select Insert > Module - paste the code there.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

...with your code pasted into a MODULE and not the Sheet Object Code window?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[wyvern621]

Hey Geoff...

I'm Excel challenged...

Where do you find a standard module in excel? I thought they were all either sheet or workbook modules.

Forgive my ignorance...

ChiTownDiva

 

[SkipVought]

Insert > Module

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

Right click on the Sheet or ThisWorkbook and select Insert > Module - paste the code there.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Slowpoke! ;-)

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[wyvern621]

Hey Guys...you all get a star...

Figured out how to get a module and John's code works perfectly...

Skip, not sure why your code won't work for me, but I sill get that #Name? error--you get a star for being patient with me...

Thanks again...


ChiTownDiva