Color a Shape the same color as a Cell - How?

**barryleajo** · 12-11-2010, 06:38 PM

Hi Everyone
I have some cells (x 13) filled using Standard Colors determined originally from the Format, Fill color picker of the cell Conditional Formatting rules.
Red, Amber (Orange), Green and a Grey.
I have triangular (Auto)Shapes superimposed on the cells.
How can I determine the exact color of each cell and apply it to fill the corresponding Shape?
In this condition I don't want the triangle noticeable - but for aesthetics (ie for other conditions) I want the colors used to be consistent.
All colors (both cells and shapes) will be determined by the contents of other cells.

Can't say I yet understand the differences, pros and cons between Theme Colors, Scheme Colors, RGB colors, vb standard colors etc. Matching Amber/Orange is the troublesome one.

With thanks
barry

**pike** · 12-12-2010, 05:25 AM

Hi barryleajo,

why not just make the shape clear or

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

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

errExit:

End Sub

http://www.ureader.com/msg/103768494.aspx

**Andy Pope** · 12-12-2010, 08:07 AM

Here is an example of the transparent shape approach Pike mentions.
http://www.xldynamic.com/source/xld.CF.html#lights2

**barryleajo** · 12-13-2010, 11:51 AM

Many thanks Pike and Andy

Pike

ONE - yes using a "clear" shape sounds a really elegant solution but how do you do this. When I "fill" a cell with "No Fill" and use this to Fill the shape, it fills with White?
TWO when I tried your code standalone it worked OK thank you.
However when I embed it in mine - it keeps returning ColorIndex as -4142 (which I understand means no color?) irrespective of the actual cell color?
THREE In have read that there are 56 colors available in a palette, why then 58 in your code?
FOUR In the color picker there are 70 color swatches plus No colr - how does this stack with 56 (or 58)?

Andy

Thanks for the link and a useful approach.
My shape sits on the coloured cell and this approach could work if I could set the shape to be transparent - see ablive.
I didn't think Conditional Formatting could be used on Shapes?

Finally, if I understand correctly the +7 in Pikes code (nIdx + 7) is to do with the different treatment of color indices by Excel 2007 from previous versions.
If i settled on using RGB to deal with colors does this make the code anymore portable across different versions?

I do understand if there are too many questions here!
As ever many thanks though
barry

**Andy Pope** · 12-13-2010, 12:20 PM

56 colours in xl2003. Not a limit in xl2007.

The colour swatch in xl2007 uses 10 colours with variations (accents)

To make your shape transparent you need to do the following.
Copy the shape and Paste > As Picture > Paste as picture.
The on the Picture Format contextual tab use the Format > Adjust > Recolor > Set transparent color.