This is probably how I'd do it (different from your approach).

I'd create a Green Smiley, a Yellow Smiley and a Red Smiley. I'd then
create a series for each color of smiley.

I'd then add the custom marker for each series as described here

http://peltiertech.com/Excel/ChartsH...omMarkers.html

HTH,
Barb Reinhardt

"NG" wrote:

Barb,

Thanks for your help, Well what i did is create a dynamic chart, but what i
needed is to have the smiley change color based on the new data gathered. For
example, A1=90, a yellow face occurs, I gather new data for A2=99, a green
face occurs, A3= 50, a red face occurs and so on. Evy week i have new data
therefore when entered in a new cell i want that new cell to displace the
smiley color.

Thanks

"Barb Reinhardt" wrote:

It also sounds like you're trying to make a dynamic chart. Take a look here
for more info on that:

http://peltiertech.com/Excel/Charts/...umnChart1.html

"NG" wrote:

Hi,
I copied this formula from another post:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("$A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Shapes("AutoShape 4").Select
If Range("A1").Value 90 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10

End If
ActiveCell.Select
Application.EnableEvents = True
End Sub

With this formula the Smiley changes green after 90, and red below that,
what i want is to have the smiley green after 90, yellow between 85-90, red
below 85. How can i modify this formula.

Once this step is done what i need is to insert the smiley on the upper
right hand corner of a Column/stack column chart. So when the data is entered
the face changes automatically.

Finally, because i have new data based on a week, i would like to have the
smiley take over the new data in the new cell, basically like a
=offset(Sheet!,1,0,counta($A:$A)-1,1) on a graph. Is it possible I know I may
be asking for a lot but ive been trying to figure out this for 3 weeks.


Thanks