Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using a macro to copy the cell contents (number data and the color format of the numbers) from one workbook into another but it's not carrying across the color formatting.

I have two workbooks:

  1. a workbook (TLC Assess 4 Findings) with client scientific data
  2. some of the data from the first workbook displayed in a more user friendly form. The data has three colors: Black = normal, Red = Above Range and Blue = Within Range.

I have a macro assigned to a button in the second workbook that pulls the data from the first workbook. It all works perfectly copying over the data except it doesn't copy over the color associated with the data.

The macro is basically:

  1. Enter the first cell on Workbook 2 with this formula:
    ='[TLC Assess 4 Findings.xls]Report'!$E$9
  2. Go to the next cell and do the same for all the cells in the section to be copied.
  3. End Macro.

Push button all the data comes over correct, but the color does not. It is either black or whatever color was previously used in that cell on workbook 2.

I also tried Copy and Paste Special with the same result. I have also tried the Format Painter.

How do I copy the data along with the color format in my macro?

share|improve this question
    
so you are entering a formula linking to the source cells, not the actual content? –  datatoo Aug 13 '11 at 21:42
    
Do the two workbooks have the same palette? –  variant Aug 15 '11 at 21:45

1 Answer 1

I'm not sure how your code looks like, or if Workbook 1 has any Conditional Formatting in it that aren't being copied over properly; but here's one of the ways you can "copy" the format of a cell in one workbook to a cell in another workbook (assuming both workbooks are open):

Workbooks("SOURCE.xls").Sheets("SHEETNAME").Range("A1").Interior.Color = _
Workbooks("DESTINATION.xls").Sheets("SHEETNAME").Range("A1").Interior.Color

For font colors:

Workbooks("SOURCE.xls").Sheets("SHEETNAME").Range("A1").Font.Color = _
Workbooks("DESTINATION.xls").Sheets("SHEETNAME").Range("A1").Font.Color

Replace the file names, sheet names and range values to match those of your files.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.