Smiley changes colors based on a number in a cell

[NG]

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

 

[Barb Reinhardt]

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

 

[Barb Reinhardt]

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/DynamicColumnChart1.html

 

[NG]

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]

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/ChartsHowTo/CustomMarkers.html

HTH,
Barb Reinhardt

 

[NG]

Barb,

I made the faces to change when a new number is enter on a new cell. Now
what i would really need help on is copying the smiley to a chart, and having
this smiley change colors, i tried but it does not work because the name of
the smiley changes once copied and paste on the chart.

Thanks

 

[Joel]

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]

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]

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.