VBA copy paste format - font color changes from white to red when pasted

[wetruesley]

Hi,

I'm using VBA to copy the format of a cell from one workbook to another. Here is the code:

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

When I paste with this code it works perfectly fine on my workstation using both Excel 2003 and 2007. However, when the end user tries the font is changed from White to Red, with Excel 2003,7,10 and 13 (I've only personally seen it on his workstation using 2003). If any other color is selected it is pasted correctly eg. blue font shows as blue font. This issue only occurs with the white font. Suggestions?

Thanks,
Wesley

 

[Jerry Sullivan]

Hi Wesley and Welcome to the Board,

I don't think this is a problem with the VBA code per se and you would probably get the same results manually copying and pasting formats between the same ranges of the same workbooks. Give that a try to confirm that's the case.

I've seen something like what you describe when pasting from .xls workbooks to .xlsx workbooks.
I'm speculating a bit here, but one possible explanation is that the Font.Color property of Selection that appears to have White font in the .xls file doesn't map exactly to one of the 57 Font.ColorIndex values. In that case, Excel tries to approximate the ColorIndex. If it approximates that ColorIndex to 3 instead of 0 or 2, then the font could appear as Red when pasted into another workbook.

See this article for an explanation of Excel's Color and ColorIndex system.
Adding Color to Excel 2007 Worksheets by Using the ColorIndex Property

When you say "on my workstation using both Excel 2003 and 2007" and "[on his workstation using]...Excel 2003,7,10 and 13", do you both have multiple versions of Excel installed on your system? Or are you each only using one Excel version and referring to files saved to different file formats (.xls versus .xlsx)?

Either way, please clarify what version of Excel and file type is being used by both the Source Workbook and the Target Workbook when the user has the font go from white to red.