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

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
>
>
>
>

Also, Conditional Formatted colors will override any normal color formatting.
--
Jim Cone
Portland, Oregon USA




"Josh Sale"
wrote in message
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

And to further clarify the issue, in Excel 2003 and earlier, each workbook
has its own color palette. If you change the colors in one workbook, it
affects only that workbook. If the color palette in two workbooks are
different, then your code will not have the effects you want. Assigning the
color index from workbook A to workbook B merely uses the color in the same
position on the palette, whether they are the same or not. Assigning the
color from workbook A to workbook B uses the color on the palette of
workbook B that Excel decides is closest to the color that is requested.
Sometimes it's close, and sometimes it's way off.

Only if the palettes are the same will either approach work reliably. You
could change the color palette of the target workbook, but that may tick off
the owner of the workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ShaneDevenshire" <(E-Mail Removed)> wrote in
message news:AC88454F-894E-45F6-A0A2-(E-Mail Removed)...
> 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
>>
>>
>>
>>

The conditional colors will appear in the cell, but the cell's color index
will reflect the color disregarding any conditional formatting.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jim Cone" <(E-Mail Removed)> wrote in message
news:ui7ej$(E-Mail Removed)...
>
> Also, Conditional Formatted colors will override any normal color
> formatting.
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
>
> "Josh Sale"
> wrote in message
> 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
>
>
>

A bit more, you quote "SourceRange.ColorIndex" three times in your code iso
"SourceRange.Interior.ColorIndex". It doesn't look a typo confined to this
post and wonder if you are running the under On Error Resume next.

That aside, you might get incorrect results, even after copying the palette
from the Source wb to the Target wb, if SourceRange contains mixed formats.
Either work with SourceRange(1) or if you want more detail
dim vClrIdx as Variant
vClrIdx = SourceRange.Interior.ColorIndex
If IsNull(vClrIdx) then ' mixed formats

If you are woriking with the Font you can get mixed formats even within a
single cell.
vFntClrIdx = SourceRange(1).Font.ColorIndex
If IsNull(vClrIdx) then ' mixed formats
vFntClrIdx = SourceRange(1).Characters(1,1).Font.ColorIndex

Others have clarified what you need to know about palette, colorindex and
colors, but just to add - having copied the palette over the once it should
be OK to work with only with colorindex.

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod> wrote in message
news:(E-Mail Removed)...
>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
>
>
>

Thanks to everybody for their comments and suggestions. Rather than
replying to each individual posting let me try make a general response here.

First, thanks for the general explanation of the palette. This helps round
out my mental model of how this stuff works and special thanks to Jon for
explaining the difference between using .Color vs .ColorIndex (palette
position vs "closest" color).


Second, let me add a bit more context about what I'm trying to do here. The
source workbook just contains formatting information ... its sort of a
template. The user can format that workbook to control how various bits of
data in the target workbook are formatted. So there is no particular
problem in copying the entire palette from the source workbook to the target
workbook (which I'm doing with assignment statement from my original
posting).


Third, I think the user formatted the target workbook under Excel 2007.
However the workbook was opened in compatibility mode so it would appear
that the palette continues to be limited to 56 colors and not 16m.


Fourth, when I test under Excel 2007, the first assignment statement
(TargetRange.Interior.Color = SourceRange.Interior.Color) seems to do the
right thing (the background color gets a kind of grey color) but the second
statement (TargetRange.Interior.ColorIndex =
SourceRange.Interior.ColorIndex) messes things up (the background color
becomes light yellow). The source interior Color is 14806254 and the source
interior ColorIndex is 19 which resolves to 13434879 when looked up in the
source palette.

Question 1: Wouldn't you expect the value of a cell's background Color to
equal that same cell's translated ColorIndex?


Fifth, when I test under Excel 2003 I immediately notice that the background
color of the cell in the source workbook has the light yellow color (not the
grey it had under Excel 2007). If I examine that cell, I find that both its
Color and translated ColorIndex are both equal to 13434879. So in this
case, it doesn't matter what kind of assignments I make in my code, I'm
never going to get the grey and am always going to get the light yellow.

Question 2: Given that the source workbook was saved in Excel 2007 in
compatibility mode, wouldn't you expect for the cells colors to be more
faithfully rendered in Excel 2003?


Thanks again for all of the help and suggestions.

josh





"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> And to further clarify the issue, in Excel 2003 and earlier, each workbook
> has its own color palette. If you change the colors in one workbook, it
> affects only that workbook. If the color palette in two workbooks are
> different, then your code will not have the effects you want. Assigning
> the color index from workbook A to workbook B merely uses the color in the
> same position on the palette, whether they are the same or not. Assigning
> the color from workbook A to workbook B uses the color on the palette of
> workbook B that Excel decides is closest to the color that is requested.
> Sometimes it's close, and sometimes it's way off.
>
> Only if the palettes are the same will either approach work reliably. You
> could change the color palette of the target workbook, but that may tick
> off the owner of the workbook.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "ShaneDevenshire" <(E-Mail Removed)> wrote in
> message news:AC88454F-894E-45F6-A0A2-(E-Mail Removed)...
>> 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
>>>
>>>
>>>
>>>
>
>

Josh -

Let me disabuse you of one notion. The color systems of Excel 2003 and 2007
are completely different and essentially incompatible. Don't assume there's
any magic in compatibility mode that will preserve custom colors across
versions. Compatibility mode means Excel won't try to put a value into cell
IW65537.

The Excel 2003 palette has 56 colors. Excel 2007 has a theme with (I think)
eight theme-related colors, from which eight sets of shades and tints are
available. You also have access to the rest of the 256^3 colors, but unlike
palette and theme colors, objects colored by these colors do not all update
if you change some kind of global color assignment.

Excel 2007 recognizes Excel 2003's palette, after a fashion. If a workbook
was assigned a customized palette in 2003, this custom palette is recognized
in 2007. However, the palette cannot be changed in 2007 (as far as I can
tell), and moving between versions is likely to break color assignments.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Josh Sale" <jsale@tril dot cod> wrote in message
news:(E-Mail Removed)...
> Thanks to everybody for their comments and suggestions. Rather than
> replying to each individual posting let me try make a general response
> here.
>
> First, thanks for the general explanation of the palette. This helps
> round out my mental model of how this stuff works and special thanks to
> Jon for explaining the difference between using .Color vs .ColorIndex
> (palette position vs "closest" color).
>
>
> Second, let me add a bit more context about what I'm trying to do here.
> The source workbook just contains formatting information ... its sort of a
> template. The user can format that workbook to control how various bits
> of data in the target workbook are formatted. So there is no particular
> problem in copying the entire palette from the source workbook to the
> target workbook (which I'm doing with assignment statement from my
> original posting).
>
>
> Third, I think the user formatted the target workbook under Excel 2007.
> However the workbook was opened in compatibility mode so it would appear
> that the palette continues to be limited to 56 colors and not 16m.
>
>
> Fourth, when I test under Excel 2007, the first assignment statement
> (TargetRange.Interior.Color = SourceRange.Interior.Color) seems to do the
> right thing (the background color gets a kind of grey color) but the
> second statement (TargetRange.Interior.ColorIndex =
> SourceRange.Interior.ColorIndex) messes things up (the background color
> becomes light yellow). The source interior Color is 14806254 and the
> source interior ColorIndex is 19 which resolves to 13434879 when looked up
> in the source palette.
>
> Question 1: Wouldn't you expect the value of a cell's background Color to
> equal that same cell's translated ColorIndex?
>
>
> Fifth, when I test under Excel 2003 I immediately notice that the
> background color of the cell in the source workbook has the light yellow
> color (not the grey it had under Excel 2007). If I examine that cell, I
> find that both its Color and translated ColorIndex are both equal to
> 13434879. So in this case, it doesn't matter what kind of assignments I
> make in my code, I'm never going to get the grey and am always going to
> get the light yellow.
>
> Question 2: Given that the source workbook was saved in Excel 2007 in
> compatibility mode, wouldn't you expect for the cells colors to be more
> faithfully rendered in Excel 2003?
>
>
> Thanks again for all of the help and suggestions.
>
> josh
>
>
>
>
>
> "Jon Peltier" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> And to further clarify the issue, in Excel 2003 and earlier, each
>> workbook has its own color palette. If you change the colors in one
>> workbook, it affects only that workbook. If the color palette in two
>> workbooks are different, then your code will not have the effects you
>> want. Assigning the color index from workbook A to workbook B merely uses
>> the color in the same position on the palette, whether they are the same
>> or not. Assigning the color from workbook A to workbook B uses the color
>> on the palette of workbook B that Excel decides is closest to the color
>> that is requested. Sometimes it's close, and sometimes it's way off.
>>
>> Only if the palettes are the same will either approach work reliably. You
>> could change the color palette of the target workbook, but that may tick
>> off the owner of the workbook.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "ShaneDevenshire" <(E-Mail Removed)> wrote in
>> message news:AC88454F-894E-45F6-A0A2-(E-Mail Removed)...
>>> 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
>>>>
>>>>
>>>>
>>>>
>>
>>
>
>

Wow!

Not what I expected to hear.

Thanks ... even if its bad news!

josh




"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Josh -
>
> Let me disabuse you of one notion. The color systems of Excel 2003 and
> 2007 are completely different and essentially incompatible. Don't assume
> there's any magic in compatibility mode that will preserve custom colors
> across versions. Compatibility mode means Excel won't try to put a value
> into cell IW65537.
>
> The Excel 2003 palette has 56 colors. Excel 2007 has a theme with (I
> think) eight theme-related colors, from which eight sets of shades and
> tints are available. You also have access to the rest of the 256^3 colors,
> but unlike palette and theme colors, objects colored by these colors do
> not all update if you change some kind of global color assignment.
>
> Excel 2007 recognizes Excel 2003's palette, after a fashion. If a workbook
> was assigned a customized palette in 2003, this custom palette is
> recognized in 2007. However, the palette cannot be changed in 2007 (as far
> as I can tell), and moving between versions is likely to break color
> assignments.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:(E-Mail Removed)...
>> Thanks to everybody for their comments and suggestions. Rather than
>> replying to each individual posting let me try make a general response
>> here.
>>
>> First, thanks for the general explanation of the palette. This helps
>> round out my mental model of how this stuff works and special thanks to
>> Jon for explaining the difference between using .Color vs .ColorIndex
>> (palette position vs "closest" color).
>>
>>
>> Second, let me add a bit more context about what I'm trying to do here.
>> The source workbook just contains formatting information ... its sort of
>> a template. The user can format that workbook to control how various
>> bits of data in the target workbook are formatted. So there is no
>> particular problem in copying the entire palette from the source workbook
>> to the target workbook (which I'm doing with assignment statement from my
>> original posting).
>>
>>
>> Third, I think the user formatted the target workbook under Excel 2007.
>> However the workbook was opened in compatibility mode so it would appear
>> that the palette continues to be limited to 56 colors and not 16m.
>>
>>
>> Fourth, when I test under Excel 2007, the first assignment statement
>> (TargetRange.Interior.Color = SourceRange.Interior.Color) seems to do the
>> right thing (the background color gets a kind of grey color) but the
>> second statement (TargetRange.Interior.ColorIndex =
>> SourceRange.Interior.ColorIndex) messes things up (the background color
>> becomes light yellow). The source interior Color is 14806254 and the
>> source interior ColorIndex is 19 which resolves to 13434879 when looked
>> up in the source palette.
>>
>> Question 1: Wouldn't you expect the value of a cell's background Color
>> to equal that same cell's translated ColorIndex?
>>
>>
>> Fifth, when I test under Excel 2003 I immediately notice that the
>> background color of the cell in the source workbook has the light yellow
>> color (not the grey it had under Excel 2007). If I examine that cell, I
>> find that both its Color and translated ColorIndex are both equal to
>> 13434879. So in this case, it doesn't matter what kind of assignments I
>> make in my code, I'm never going to get the grey and am always going to
>> get the light yellow.
>>
>> Question 2: Given that the source workbook was saved in Excel 2007 in
>> compatibility mode, wouldn't you expect for the cells colors to be more
>> faithfully rendered in Excel 2003?
>>
>>
>> Thanks again for all of the help and suggestions.
>>
>> josh
>>
>>
>>
>>
>>
>> "Jon Peltier" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> And to further clarify the issue, in Excel 2003 and earlier, each
>>> workbook has its own color palette. If you change the colors in one
>>> workbook, it affects only that workbook. If the color palette in two
>>> workbooks are different, then your code will not have the effects you
>>> want. Assigning the color index from workbook A to workbook B merely
>>> uses the color in the same position on the palette, whether they are the
>>> same or not. Assigning the color from workbook A to workbook B uses the
>>> color on the palette of workbook B that Excel decides is closest to the
>>> color that is requested. Sometimes it's close, and sometimes it's way
>>> off.
>>>
>>> Only if the palettes are the same will either approach work reliably.
>>> You could change the color palette of the target workbook, but that may
>>> tick off the owner of the workbook.
>>>
>>> - Jon
>>> -------
>>> Jon Peltier, Microsoft Excel MVP
>>> Tutorials and Custom Solutions
>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>> _______
>>>
>>>
>>> "ShaneDevenshire" <(E-Mail Removed)> wrote in
>>> message news:AC88454F-894E-45F6-A0A2-(E-Mail Removed)...
>>>> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
>