This should help with the first part.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Excel.Range
Dim myShape As Excel.Shape

Set r = Me.Range("$A1")
If Target.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("$A$1")) Is Nothing Then Exit Sub

Set myShape = Me.Shapes("AutoShape 4")
If Target.Value 90 Then
myShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
ElseIf Target.Value 85 Then
myShape.Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
myShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
End If
End Sub

HTH,
Barb Reinhardt

"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