N
NG
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
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