What error are you getting. With Power Point you probably had one chart per
graph and the view didn't change names. what yo have know sounds like there
are multiple charts which is the problem.

"NG" wrote:

Joel,

I couldnt make you code work. How about this.. How can i link this happy
face to microsoft powerpoint, to where the faces update automatically once
opening powerpoint. I have done this with the charts, but i cant paste
picture link to powepoint.

Thanks

"Joel" wrote:

I modified the code but don't know the name of the chart. Change Chart 33 to
the name of the chart. the problem with switch to a different chart is yo
can't tell which chart on the sheet is the lastest chart. If yo had a title
of the chart that changed then you can search through all the charts to
determine the lastest chart. without an algorithm to determine which chart
is the newest you can't have your Smiley move.

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyChart = ActiveSheet.Shapes("Chart 33")

Set r = Range("$A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False

Set Smiley = ActiveSheet.Shapes("AutoShape 4")
Select Case Target.Value
Case Is 90
Smiley.ShapeRange.Fill.ForeColor.SchemeColor = 11
Case Is = 85
Smiley.ShapeRange.Fill.ForeColor.SchemeColor = 6
Case Else
Smiley.ShapeRange.Fill.ForeColor.SchemeColor = 10
End Select

Smiley.Left = MyChart.Left + MyChart.Width - Smiley.Width
Smiley.Top = MyChart.Top


End If

Application.EnableEvents = True
End Sub
"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