Trouble setting background color of Worksheet

[Joseph Geretz]

I'm trying to set the background color for the entire worksheet. If there's
a quicker way to do this, other than by grabbing a reasonably large range,
then please let me know and I'll use that approach:

Here's the approach I am attempting:

TargetWorksheet.Range("A1:AZ1024").Interior.Color =
SourceWorksheet.Cells(1, 1).Interior.Color

What I'm doing is setting the color of the target worksheet to the color of
the source worksheet. The color I expect is:

C1 9B 7C or 12688251 - a nice pale blue.

I've verified that SourceWorksheet.Cells(1, 1).Interior.Color does in fact
evaluate to 12688251, but what I'm actually seeing is light grey, rather
than pale blue. Even if I try this using the desired value as a literal
constant, I still get the light grey. I know the code statement is doing
something, since without it the target worksheet shows up white and a
literal constant of 0 gives me a black backgound, &HFF00FF gives me bright
lavender, but for some reason, C19B7C (12688251) gives me light grey, even
though in the source worksheet is shows nicely as pale blue.

What am I doing wrong?

Thanks!

- Joseph Geretz -

 

[Guest]

Try this:
TargetWorksheet.Cells.Interior.Color = SourceWorksheet.Cells(1,
1).Interior.Color

 

[Joseph Geretz]

Hi Martin,

Thanks for your help, but this does not work for me. All of the following
syntaxes give me exactly the same result.

TargetWorksheet.Range("A1:AZ1024").Interior.Color =
SourceWorksheet.Range("A2").Interior.Color
TargetWorksheet.Cells.Interior.Color =
SourceWorksheet.Range("A2").Interior.Color
TargetWorksheet.Range("A1:AZ1024").Interior.Color = 12688252
TargetWorksheet.Cells.Interior.Color = 12688252

What's odd is, that after each line is executed, VB6 Debug shows me that
TargetWorksheet.Cells.Interior.Color = 9868950, rather than 12688252. Why is
this value, which works in the source worksheet, being overridden to grey in
the target worksheet?

Thanks for your help,

- Joe Geretz -

 

[Joseph Geretz]

I think the problem is that this color is a custom color. It exists in the
source workbook, but not in the target workbook. I'm not enough of an expert
to explain why this might be the problem (after all the value is an RGB
value which defines the color, doesn't it? What more is needed?) but
empirircally I can see that this is the case, since I'm able to get the code
to work for Excel sheets in which I am using the stock color to set the
background. So perhaps, setting the background for the target sheet is a
two-step process:

1. Ensure that the color is defined on the color palette of the target
worksheet
2. Set the cells of the target worksheet to that background color.

Can you tell me how to accomplish step #1?

Thanks!

- Joseph Geretz -