Extract data by line color?

g48dd · 02-03-2011, 06:20 AM

Excel 2003, I have about 1000 lines and some of the lines are in different colors. Is it possible to extract 12 columns of data by line color? I looked for possible common link between the data so that I could sort and then copy. There is no common link for all other than all these phones a private. There is no column that denotes private, I just know that is what the common thread is and management needs a count. They are recognized on my spreadsheet by the color yellow. I am going to do this manually one by one but was wondering if there was a way to extract the data by VBA or formula?

Thanks
Ken

**martindwilson** · 02-03-2011, 06:46 AM

you could use this udf in a new module 9see link in my sig how to insert code0

Please Login or Register  to view this content.

use as =getcolor(a1)
drag down and sort all the same colours wil group together

g48dd · 02-03-2011, 07:25 AM

Thank you, about modules..... when I open my VBA editor I have many modules. You state to enter this code in new module not just paste into one where I have several macros. Many of my modules, I have 13 sheets and 11 modules, but modules 2,3,4,5,6,10,11 do not have any code on them. Do I need to insert the code in a new module (module 12)? Or just any module that is blank. Looking at the VBA tree it apperas that the set of modules I am looking at really have no special relationship to any one specific sheet... they work on any sheet that is in the workbook. Can I delete all the modules that have nothing on them? So I guess three questions:

1) Do I need to insert a new module / or can I just use a blank module?
2) Can I delete the modules that have nothing on them?
3) Is it best to keep 1 macro per module... unless the macro are realted, like where one macro calls another? Then should you keep those together in the same module?

I have under options... TOOLS/OPTIONS.... Require Variable Declaration.. checked

Thank you
Ken

**Cheeky Charlie** · 02-03-2011, 07:29 AM

1 You can use an existing module
2 Yes
3 Not particularly. Use modules to associate things which belong together. Creating more doesn't do any damage, but too many can be hard to manage.

Good. Require variable declaration simply writes Option Explicit at the top of every code section. It's good practice and helps prevent errors.

g48dd · 02-03-2011, 08:10 AM

I am not doing something correctly. At first I thought it was macro but then read again UDF (user defined Function) which i have never used cool.... Ok My Data is in A7:L1042, so I started with curser in cell A7, then clicked the FUNCTION Icon, box appears.... drop down arrow choose user defined.... there it is ... getcolor... my range is already highlighted but I clicked the button that allows you to choose your range and highlighted the range again.... clicked OK. It returned the value 0 in the A7 cell but the sheet did nothing, so I am using it wrong.... can you explain what it is I am doing wrong?

Ken

**martindwilson** · 02-03-2011, 08:57 AM

you say your data is in A7:L1042 i presume a whole row say a2:l20 is what would be highlited in yellow ?
then in
say M7 put =getcolour(a7) fill down to M1042 [note you cant put a range like =getcolor(a7:b20)]

g48dd · 02-03-2011, 09:38 AM

Wow nice, it took me a min I still didn't get it. I placed the Function in M7 and I kept putting in a range, your not suppose to. That is not what you told me to do... you said just type in A7 and fill down..... I am thinking how am I going to sort if it does not have a range to sort.... that is not what you want so I thought ok just try it so I typed in just A7, it returns 15???? 15 mmmm... ok fill down, so I fill down, ok so I have 15, 15, 15, 15, 6 where is getting 6? OHHHHH the numbers are color reference alright so all I have to do now sort these numbers..... Click sort Wow whole spread sheet is sorted by color thank you now I can copy and past what I need... this is huge it will save me so much work.

Thank you Ken