Excel 2007 : Pasting data into a cell only if the cell has a background color

**new.vbacoder** · 06-15-2011, 09:33 PM

I've been searching the forums, and can't specifically find what I'm looking for.

Here is my situation.

I have a csv file containing the data. I have an excel file that has an import function that opens the csv file and copies the data in there to certain columns in the worksheet.

My issue is that if I do a copy pasteSpecial, such as in the attached file, it will overwrite cells that have formulas in them.

What I would like to do is copy the data from the csv, and only paste the value of the csv into the excel file if the cell has a yellow background color.

Any help would be appreciated.

Since I can't attach a csv, I will explain what the file is. If you choose save data collection info, it will save the sheet into a csv file (basically copying the sheet). There would potentially be values in all cells shaded yellow. I have left data in there so if you need to you can create the csv.

**MarvinP** · 06-15-2011, 10:35 PM

Hi new.vbacoder,
I do similar CSV imports. I import the CSV file into a new workbook and then code to copy and paste the parts I want into the workbook that has the formulas in it. This is one solution.

On other CSV imports I code a formula into the CSV file and do calculations before I copy and paste it to my master workbook.

See http://excel.itags.org/microsoft-excel/38414/ for how to code VBA to put an Excel formula into a cell.

**new.vbacoder** · 06-16-2011, 01:25 PM

Originally Posted by MarvinP

Hi new.vbacoder,
I do similar CSV imports. I import the CSV file into a new workbook and then code to copy and paste the parts I want into the workbook that has the formulas in it. This is one solution.

On other CSV imports I code a formula into the CSV file and do calculations before I copy and paste it to my master workbook.

See http://excel.itags.org/microsoft-excel/38414/ for how to code VBA to put an Excel formula into a cell.

Thanks for the post. I'm not sure that is quite what I'm trying to accomplish. For example, the csv file would look just like the Data Collection Input sheet, minus any formatting. I want to bring in everything in column B and C from the csv file, but only if the cell is shaded yellow in the Data Collection Input sheet. The code as I have it will overwrite the sum formula in cell D9, D18, D27, etc.

I have included my code below, with where I think the paste only into cells with a background color. I just can't figure out what the code should be.

Please Login or Register  to view this content.

**MarvinP** · 06-16-2011, 01:54 PM

Hi,
My suggestion doesn't really import the CSV file into the existing workbook. I've found you can OPEN the CSV file in a new workbook and with two workbooks open copy and paste appropriate CSV data into the master .xls sheet(s).

I think this is the direction you should be going, instead of trying to import a csv file over an existing xls file.

**new.vbacoder** · 06-16-2011, 02:43 PM

Originally Posted by MarvinP

Hi,
My suggestion doesn't really import the CSV file into the existing workbook. I've found you can OPEN the CSV file in a new workbook and with two workbooks open copy and paste appropriate CSV data into the master .xls sheet(s).

I think this is the direction you should be going, instead of trying to import a csv file over an existing xls file.

But wouldn't that require a lot of code to equate cells in the csv with those in the worksheet (you would basically have to say cell x in the worksheet = cell x in the csv for all cells you want copied over, right??)? I've been reading up on dealing with cells that have a background color, and it seems to me like it should just be 1-2 lines in the code when doing the paste special that if the background color is yellow then paste the value, otherwise do nothing.

Thank you, though, for your recommendation. If I don't get any responses about background color, then I will take your advice (again, I was just hoping to reduce the code required).