Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting by Fill Colour
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 |
#2
|
|||
|
|||
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 "Denise Robinson" wrote: 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 |
#3
|
|||
|
|||
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 XX "Denise Robinson" wrote in message ... 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 |
#4
|
|||
|
|||
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 "aristotle" wrote in message ... 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 "Denise Robinson" wrote: 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 |
#5
|
|||
|
|||
Here's a response from a similar previous thread http://www.excelforum.com/showthread...sorting+colour -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=394647 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fill colour in Excel, it appers No fill in my computer? | Excel Discussion (Misc queries) | |||
Fill colour red | Excel Discussion (Misc queries) | |||
Can I fill colour in a excel cell from a return from an "IF" test. | Excel Worksheet Functions | |||
- colour fill in excel | Excel Discussion (Misc queries) | |||
Sum cells according to colour fill | Excel Discussion (Misc queries) |