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:
- a workbook (TLC Assess 4 Findings) with client scientific data
- 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:
- Enter the first cell on Workbook 2 with this formula:
='[TLC Assess 4 Findings.xls]Report'!$E$9
- Go to the next cell and do the same for all the cells in the section to be copied.
- 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?