Sorting by Fill Colour

[Denise Robinson]

Hi all

I have a large database (23,000 rows) and many of the rows have coloured
backgrounds as a form of coding to myself.

Is there anyway you can sort by fill colour?

eg. 1000 are Red to indicate No interest and I want to easily delete them in
one go rather than scrolling through 23,000 and deleteing individually.

I have a feeling someone will suggest writing a Macro thingy which I'm
afraid my brain will not be able to cope with!

Thank you
Denise

 

[Guest]

Denise,

This code is for a UDF that defines cells by their colour. You can use this
function to populate a new column according to the colour name / index, and
then apply a filter and delete accordingly.

Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer

'Written by Dave Hawley of OzGrid.com
Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51
Case 49
strColor = "Dark Teal"
iIndexNum = 49
Case 11
strColor = "Dark Blue"
iIndexNum = 11
Case 55
strColor = "Indigo"
iIndexNum = 55
Case 56
strColor = "Gray-80%"
iIndexNum = 56
Case 9
strColor = "Dark Red"
iIndexNum = 9
Case 46
strColor = "Orange"
iIndexNum = 46
Case 12
strColor = "Dark Yellow"
iIndexNum = 12
Case 10
strColor = "Green"
iIndexNum = 10
Case 14
strColor = "Teal"
iIndexNum = 14
Case 5
strColor = "Blue"
iIndexNum = 5
Case 47
strColor = "Blue-Gray"
iIndexNum = 47
Case 16
strColor = "Gray-50%"
iIndexNum = 16
Case 3
strColor = "Red"
iIndexNum = 3
Case 45
strColor = "Light Orange"
iIndexNum = 45
Case 43
strColor = "Lime"
iIndexNum = 43
Case 50
strColor = "Sea Green"
iIndexNum = 50
Case 42
strColor = "Aqua"
iIndexNum = 42
Case 41
strColor = "Light Blue"
iIndexNum = 41
Case 13
strColor = "Violet"
iIndexNum = 13
Case 48
strColor = "Gray-40%"
iIndexNum = 48
Case 7
strColor = "Pink"
iIndexNum = 7
Case 44
strColor = "Gold"
iIndexNum = 44
Case 6
strColor = "Yellow"
iIndexNum = 6
Case 4
strColor = "Bright Green"
iIndexNum = 4
Case 8
strColor = "Turqoise"
iIndexNum = 8
Case 33
strColor = "Sky Blue"
iIndexNum = 33
Case 54
strColor = "Plum"
iIndexNum = 54
Case 15
strColor = "Gray-25%"
iIndexNum = 15
Case 38
strColor = "Rose"
iIndexNum = 38
Case 40
strColor = "Tan"
iIndexNum = 40
Case 36
strColor = "Light Yellow"
iIndexNum = 36
Case 35
strColor = "Light Green"
iIndexNum = 35
Case 34
strColor = "Light Turqoise"
iIndexNum = 34
Case 37
strColor = "Pale Blue"
iIndexNum = 37
Case 39
strColor = "Lavendar"
iIndexNum = 39
Case 2
strColor = "White"
iIndexNum = 2
Case Else
strColor = "Custom color or no fill"
End Select

If ColorName = True Or _
strColor = "Custom color or no fill" Then
CellColor = strColor
Else
CellColor = iIndexNum
End If

End Function

 

[Jim Cone]

Denise,

I have just completed a "macro thingy" that does what you want.
It is an Excel add-in ("Special Sort") that provides nine additional sort options...

color - font color or cell color
text with numbers in numerical sequence
just the numbers in the text
length of text
text in reverse
date - day, month, year or day of week
excluding leading articles (a, an, the)
text before a specified separator
text after a specified separator

There is an option to include borders with the sorted cells.
It looks and acts somewhat like the regular Excel sort utility.
..
Comes with a two page Word.doc install/use file.
Available -free- upon direct request.
Remove XXX from my email address.

Regards,
Jim Cone
San Francisco, USA
(e-mail address removed)


Hi all

I have a large database (23,000 rows) and many of the rows have coloured
backgrounds as a form of coding to myself.

Is there anyway you can sort by fill colour?

eg. 1000 are Red to indicate No interest and I want to easily delete them in
one go rather than scrolling through 23,000 and deleteing individually.

I have a feeling someone will suggest writing a Macro thingy which I'm
afraid my brain will not be able to cope with!

Thank you
Denise

 

[Denise Robinson]

Sorry, I think I am going to need a little more help?

I am looking at my sheet (copied sample to test) and gone to insert function
and gone blank!

I don't think I understand 'Function' or Macros.....

Will look at excel help and try and understand the term 'function' first,
before I read this again.

It looks like I will have to learn to walk first rather than trying to
sprint!

Thanks
Denise

 

[Paul Sheppard]

Here's a response from a similar previous thread

http://www.excelforum.com/showthread.php?t=359514&highlight=sorting+colour