Hi,

There is nothing wrong with the approach you are using, the problem lies in
the custom color palette. Excel does not look at the color, it looks at the
position of the color on the palette. One alternative would be to add code
to set the color palette to the colors you want. This might irritate the
users.

Excel 2003 supports 56 colors in the spreadsheet and therein lies the
problem. 2007 now supports something like 16 million.

Here is the code that sets a color using the RGB scale:

Sub AddColor()
ActiveWorkbook.Colors(27) = RGB(208, 255, 30)
End Sub

Notice what it does is replace the color at a given position in the palette.
This means that where ever the user has used color 27 it will not be this
new color.
--
Thanks,
Shane Devenshire


"Josh Sale" wrote:

I have what I think should be a very simple problem but I can't quite puzzle
it out. Basically I want to copy the background color from one arbitrary
cell to another arbitrary cell. I want this process to work even if the
user has formatted the source cell using a color from a custom color
palette.

I currently have code that looks more or less like this:

If SourceRange.ColorIndex < xlNone Then
TargetRange.Interior.ColorIndex = SourceRange.ColorIndex
TargetRange.Interior.Color = SourceRange.Color
End If

Mostly this seems to work OK. However sometimes it fails (that is the wrong
background color appears). I think in these cases it because the source
range has a custom background color.

Source and Target are always in different workbooks so I have a line like
this in my code:

TargetRange.Parent.Parent.Colors = SourceRange.Parent.Parent.Colors

to try to copy the custom color palette from the source workbook into the
target workbook.

I have to admit I'm fuzzy on difference between the Color and ColorIndex
properties. I gather the former is an rgb value and the latter is an index
into the palette. Do I need to copy both to get my colors? One? Which
one?

TIA

josh