Shape color index does not match cell color index

[Chrisso]

Hi All

I have writen some code so that a user can add a callout shape onto a
cell.

I would like to color the callout shape the same color as the cell the
user selected.

shp.Fill.ForeColor.SchemeColor = r.Interior.ColorIndex

However it seems that the SchemeColor for shapes DOES NOT equal the
ColorIndex used for cells.

Should I use a different property or is there a mapping? Or is this
too hard?

Cheers for any ideas,
Chrisso

 

[Andy Pope]

Hi,

This may help you at least understand the problem.
http://spreadsheetpage.com/index.php/comments/exploring_theme_colors/#c

Cheers
Andy

 

[OssieMac]

Don't guarantee it will work but try the following:-

shp.Fill.ForeColor.RGB = r.Interior.Color

I was able to get it to work using the following test code so it might point
you in the right direction:-

ActiveSheet.Shapes("Rectangle 1").Select

Selection.ShapeRange.Fill.ForeColor.RGB = Range("A1").Interior.Color

 

[Peter T]

For IndexColor to SchemeColor add 7

If you are using Excel 97-2003 following should work (except CF colours will
override). Otherwise, sadly, the link Andy referred you to will explain some
of the difficulties.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nIdx As Long
Dim shp As Shape

On Error GoTo errExit
Set shp = Me.Shapes("AutoShape 1") ' < name to suit
nIdx = Target(1).Interior.ColorIndex
If nIdx < 1 Then nIdx = 58
shp.Fill.ForeColor.SchemeColor = nIdx + 7

errExit:

End Sub

Regards,
Peter T

 

[Chrisso]

Thanks all posters - very useful and helpful information and links.

Cheers
Chrisso