# Excel - Copy Row based on yellow highlighting



## imogul (Feb 28, 2011)

I'm pulling my hair out cause I figured this out last year and now I can't find it.

I am given this spread sheet monthly that has conditional formatting applied already. Column B has various notes, and the ones with the word "faculty" are highlighted by conditional formatting. 

I need to copy the rows, where cell B is highlighted yellow, into sheet 2. Any help would be appreciated!


----------



## etaf (Dec 28, 2008)

what condition highlights the cell yellow - perhaps you can sort that column and then just copy the rows ?

what version of excel are you using


----------



## mdawson69 (Sep 26, 2011)

If I am reading this correctly, you in effect only wish to copy the rows that have "faculty" in Column B. The conditional formatting is irrelevant even though the highlighting is contingent upon the same criterion.

What you actually want to do is filter your data based on the contents of Column B and then capture the visible records (rows). To do this, first select the cell in Column B just above the first row of data. You have not indicated which version of Excel you are using, but assuming that you are using Excel 2007 or Excel 2010, then go to the Data tab in the Ribbon, then click the Filter button in the Sort & Filter group. With data filtering now active you should see the active row will now have filtering drop downs.

Click the drop down for Column B and deselect (Select All). Now check the entry for "faculty" and click OK. Now only the rows with "faculty" in Column B will be visible. When you select and copy the visible rows, the hidden data should not be present when you paste into another worksheet.

If you are not using Excel 2007 or higher, I can post instructions for performing this procedure in Excel 2003. If I recall correctly, there is a Filter command in the Data menu to activate filtering, but I would have to launch my Win XP virtual machine to verify.


----------



## imogul (Feb 28, 2011)

Unfortunately its not that easy. Its a note's section so in one cell it might say "faculty attending", the next "as applies to faculty", then "where faculty has approval rights", and etc.

Also this is a large sheet with hundreds of rows and 13 worksheets so I'm trying to automate things as much as I can. Copying these rows is the last step I need in my macro to finish that.


----------



## RSpecianjr (Jan 20, 2010)

Hey imogul,

Just a few questions:

What are the worksheet names we are dealing with? (source and destination)
Is the data in column B Consistent? Meaning, no spaces?
How many columns do you need to copy? Is it Column A to D? Do you want to skip columns? Rearrange data at all?

We will need to know the color code of the yellow you are using... You can find it by running this macro:



> sub test()
> msgbox worksheet("your source worksheet name").Range("a cell that has the color").Interior.ColorIndex
> end sub


As always, if you can post the workbook, it helps us a lot.

Regards,

Robert D. Specian Jr.


----------



## mdawson69 (Sep 26, 2011)

imogul said:


> Unfortunately its not that easy. Its a note's section so in one cell it might say "faculty attending", the next "as applies to faculty", then "where faculty has approval rights", and etc.
> 
> Also this is a large sheet with hundreds of rows and 13 worksheets so I'm trying to automate things as much as I can. Copying these rows is the last step I need in my macro to finish that.


Ah. That is different from what was inferred in your original post. In that case, filtering will not work, but even with a macro, the conditional formatting will still be non sequitur, as you would be making selections based on cell content: cells in Column B _containing_ the word “faculty”. A macro to do this would not be difficult, but it would be involved.

In your original post, you indicate that you want to copy the select rows to Sheet 2. Before providing methodology, can you clarify your copying process requirements. Are you copying select rows from Sheet 1 to Sheet 2, as your original post implies, or are you copying select rows from across multiple worksheets into a new worksheet?


----------



## etaf (Dec 28, 2008)

you can still use filter - just use "custom" and choose "contains" 
but if you are going to do this repeatedly a macro is probably better


----------



## MPR (Aug 28, 2010)

Someone in this thread wrote a macro that copies by highlight:

[Excel] Copy cells based on highlight color.... - Microsoft Help | DSLReports Forums


----------

