adding an autoshape to vba code
 

Hi,

I have the following formula. What it does is if i enter a value a happy
face changes colors depending on the value entered. Now i have another shape
(arrow) that i want to incorporate into this formula. THe shape would change
color and orient itself in a certain way depending on the previous cell. say:
A1=20, A2=9, what the arrow would do is show a green color and it would be
facing up. if A2=9 and A3 =40, I would like the arrow to show point down as
an increase and show it self as red. Can any one help me with this.

Formula:

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

Set r = Me.Range("$B11:$B99")
If Target.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("$B$11:$B$99")) Is Nothing Then Exit Sub

Set myShape = Sheets("Chart MT").Shapes("AutoShape 4")

If Target.Value < 7 Then
myShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
ElseIf Target.Value 12 Then
myShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
myShape.Fill.ForeColor.RGB = RGB(255, 255, 0)
End If
End Sub

Thanks,

NG