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

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.

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.

Sub ImportDataCollection()
Dim DestBook As Workbook, SourceBook As Workbook, Destsheet As Worksheet
    Dim DestCell As Range
    Dim RetVal As Boolean
     
     ' Turn off screen updating.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
     ' Set object  variables for the active  book and active cell.
    Set DestBook = ActiveWorkbook
    Set Destsheet = ActiveSheet
    Set DestCell = Destsheet.Range("B1")
    Set DestCell2 = Destsheet.Range("I1")
     
     ' Show the Open dialog box.
    RetVal = Application.Dialogs(xlDialogOpen).Show("Data Collection Input*.csv")
     
     ' If Retval is false (Open dialog canceled), exit the procedure.
    If RetVal = False Then Exit Sub
     
     ' Set an object variable for the workbook containing the text file.
    Set SourceBook = ActiveWorkbook
     
     ' Copy the project info.
    Range("B:C").Copy
         
     ' Activate the destination workbook and paste special the values
     ' from the text file.
    DestBook.Activate

     'The Paste only to cells with yellow background should probably be here'

    DestCell.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Destsheet.Name = ActiveWorkbook.ActiveSheet.Name
     
     ' Set an object variable for the workbook containing the text file.
    SourceBook.Activate
         
     ' Copy the project info.
    Range("I:J").Copy
         
     ' Activate the destination workbook and paste special the values
     ' from the text file.
    DestBook.Activate

     'The Paste only to cells with yellow background should probably be here'

    DestCell2.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Destsheet.Name = ActiveWorkbook.ActiveSheet.Name
     
     ' Close the book containing the text file.
    SourceBook.Close False

    Application.DisplayAlerts = True
    ActiveWorkbook.Sheets("Data Collection Input").Select
    ActiveSheet.Range("A1").Select

End Sub