RGB Colours Between Excel and Word

[RobHudson]

Hi

I am using Word and Excel and need to set the colour of a series on a chart in Excel to be the same colour as the background colour of a cell in a table in Word.

Here's the cunning bit...

RGB(156, 186, 90) added to a table cell in Word produces a different colour in Excel (Word appears correct).

As I undersatnd RGB() returns a Long. I am creating an array of the colours, as such:

Dim arColours(100) As Long
arColours(0) = RGB(156, 186, 90)
arColours(1) = RGB(198, 81, 74)
arColours(2) = RGB(74, 130, 189)
arColours(3) = RGB(247, 251, 0)
arColours(4) = RGB(247, 109, 0)
arColours(5) = RGB(8, 182, 173)
arColours(6) = RGB(231, 56, 156)
arColours(7) = RGB(140, 146, 222)
arColours(8) = RGB(140, 69, 0)
For intCounter = 9 To 99
    arColours(intCounter) = RGB(0, 0, 0)
Next intCounter

Word code:

For i = 0 to 8
    wdApp.Selection.Shading.BackgroundPatternColor = arColours(i)
Next i

Excel code:

For i = 0 to 8
    xlApp.ActiveChart.SeriesCollection(1).Points(i).Interior.Color = arColours(i)
Next i

My guess is that Excel and Word interpret that Long differently (Excel wrongly?). Is there a way of ensuring that it uses the correct colour?

 

[TonyJollans]

Excel workbooks (prior to 2007) have a limited palette, and you can only use the colours in the palette. When you enter an RGB value, the 'closest' colour in the palette is the one actually applied. You can change the colours in the palette with code like:

ActiveWorkbook.Colors(1) = RGB(156, 186, 90)

When you have added your colour to the palette it will then be the nearest colour to the RGB value, if entered - or you can explicitly use the palette color by index into the palette.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RobHudson]

Hi Tony

Thanks for that
I wondered whether it might be a palette issue.

Interestingly (or not!), I still get the colour difference in 2007 as well as 2003. I'm guessing the solution is to add the colours to the Excel palette either way.

Adding multiple colours to the palette - is it something like this?

'Using colour array from original post
For i = 0 to 8
    ActiveWorkbook.Colors(i+1) = arColours(i)
Next i

 

[TonyJollans]

It's a couple of years since I looked at this, but I have a feeling you may have to set them differently, or change the color type first or some such thing to be able to use RGB colours directly in 2007. It is also an area where VBA is somewhat deficient, especially in charts.

I've been meaning to get to grips with this for a long time and did start writing a web page on Excel colours to go with my Word pages, but never finished. Perhaps I'll get back onto it now.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website