Extract a list by background color

[mandarp22]

Hi all
I have a worksheet with employee’s data where some cells are highlighted. Now I want to make a new list of only rows that are highlighted with green color in new workbook. I don’t want to sort the list by color as I need the original sequence of the list. How to do it please help..

Regards
Mandar

 

[Roodey]

Hi Mandar,

Here is an example to fill the list in your existing Excelsheet. You didn't provide the conditions for the new workbook (name, etc.).

Delete List button = Clear the list
Fill List button = Fill the list

Greetings,
Roodey

 

[Ramesh]

Hi Mandarp,

I see that the cells are highlighted Red and green color in column B.

Are these cells highlighted based on some criteria? If yes, you can fetch these cells matching the Green color criteria onto column I.

Thanks,
Ramesh

 

[Hui]

Mandar

You can use a UDF to extract the cell's fill color
then use that new field to Sort

Copy the following into a code module in VBA

Function CellColor(Target As Range) As Double
  CellColor = Target.Interior.Color
End Function

Then in your file F3: =CellColor(B3)
Copy F3 down
Then sort by Column F

Enjoy

 

[vletm]

Something like this ...
You can select 'any color' to copy ...

 

[NARAYANK991]

Hi Mandar ,

You say you do not wish to sort your data by color , since you want to retain the original order.

However , the original order is always available through the first column labelled Sr. No.

And even otherwise , when Excel sorts , it always maintains the original order within the sorted data , unless a second level of sorting which disturbs the original order is specified.

See the attached file.

Narayan

 

[Hui]

Mandar

If you use the technique I described above you can then extract the records using formula

Refer to the techniques discussed here

Single Criteria
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Multiple Criteria
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

 

[mandarp22]

Hi
The solution by veltm works fine and is something I am looking for, only thing needs to fix is; when the active cell is in column B and not filled by any color, it gives me the entire list in sheet2; instead of asking to select the color in column B. can it be fixed??

Regards
Mandar

 

[vletm]

Okay...
So far 'non color' handel like 'any color',
but of course it is possible to make something like You wrote ...
Now, You can make a new selection or use 'non color'.

 

[mandarp22]

Hi
I tried technique suggested by Hui and successfully extract the data that is needed. But when the color is changed it is not updated automatically….
(the formula in column F and in cell N3 is not updated automatically)

Regards
Mandar

 

[Hui]

Change
I3: =IF(COUNTIF($F:$F,$N$3)< ROWS($I$3:I3),"",INDEX(B:B,SMALL(IF($F$3:$F$500=$N$3, ROW($B$3:$B$500 )), ROW(A1)))) Ctrl+Shift+Enter
Copy across to L3
Then Copy I3:L3 Down

N3: =CellColor(O3)+NOW()*0

Now the table will update


see attached file:

If it doesn't press F9