Shape format linked to cells?

[Rise]

I'm working on streamlining the data entry and formatting of 88 charts for our receptionist and I'm making some decent progress. Our charts basically show how the production floor is doing vs some goal, for example for scrap we have a goal of less than 2%. If you're less than or equal to 2%, you had a "green" day, if not you had a "red" day. At the top right corner of all of the charts are symbols correlating to what metric you are looking at, so the quality goal is a 5-point star. Is there a way to link the color of that star to a True/False column that I've populated for each day that states whether or not the goal was met?

Basically I want to do something like this:
based on this range of data, go to today's date
if today = true, then shape fill = green
if today = false, then shape fill = red

Is this possible without making a macro? Can I somehow link conditional formatting to the shape? If it requires a macro, what type of vba do you need to change the fill color of a shape?

[HansV]

What is the lowest version of Excel you need to support? Microsoft introduced new features for this kind of thing in Excel 2007.

[Rise]

2007 is what we use here... nothing lower. We may be upgrading to 2010 or whatever version they are up to in the near future.

[HansV]

Not exactly what you asked, but take a look at icon sets before continuing this; they can be applied using conditional formatting.

iconsets.jpg

[Rise]

yeah it's tough since I need to use a specific set of shapes that the corporation defines (arrow, star, circle, cross). I've attached an example of what I'm talking about specifically...

The arrow at the top right is red since they didn't meet their goal that day. Had they, it would be green.

[HansV]

The attached sample workbook uses two shapes, a picture link and a named formula to seemingly change the color of a shape depending on a TRUE/FALSE value.
Change the value of E2 to see the effect.
Select the picture over G2 to see that it uses the formula =Piccy. The picture was created by selecting a cell, copying it, then pasting it as Picture > Picture Link. The formula was edited manually later on.
Look at the Name Manager in the Formulas tab to see the named formula.

Sample.xlsx

For a "real" version you could move the shapes from B2:C2 to a hidden sheet.

[Rise]

wow... my mind is blown. I don't understand how it is working 100% yet but I will play around with it a bit and see if I can make it work in my chart.

[Rise]

So I managed to get it to work, much like your example but it only seems to work in a cell... is it possible to do this in a chart?

[HansV]

You have to create the picture link in a cell, but since it's a floating shape, you can drag it on top of a chart.

x399.png

[Rise]

yeah I got that far actually since my last posting - but I can't get it to show up when printing...

edit: nevermind, I got it to show up. Thanks Hans, I'll see if this looks good in the print.

[Stefan_Sand]

hi,

he can use this function:

Function ShapeCol(Name As String, Red As Byte, Green As Byte, Blue As Byte)
Application.Volatile
With ActiveSheet.Shapes(Name)
.Fill.ForeColor.RGB = RGB(Red, Green, Blue)
End With
End Function

or, if he doesn´t want to use rgb for the use of the index colours

Function ShapeCol(ShapeName, Col)
Application.Volatile
With ActiveSheet.Shapes(ShapeName)
.Fill.ForeColor.SchemeColor = Col
End With
End Function

the function can be used as an ad with nested ifs or something similar to color the shapes.

regards,
Stefan