Programmatically insert a graphic?

[Kent McPherson]

I have a spreadsheet where I want to create a traffic light view of the
data. For example:

Value 1-3 => red circle
Value 4-6 => yellow circle
Value 7-10 => green circle

The formula I'm working with looks like this:

=IF(Sheet1!B2<4,red,IF(Sheet1!B2<7,yellow,green))

The red, yellow, and green items need to pull graphic symbols which is where
I'm struggling. How can I do that?

 

[Guest]

Here's just an idea of a VBA module that could do the work for you:

Sub Circles()

Sheets("Sheet1").Activate
Select Case ActiveSheet.Range("B2")
Case Is >= 7
'Change 20, 20, 50, 50 to adjust the size and location of the circles
ActiveSheet.Shapes.AddShape(msoShapeOval, 20, 20, 50, 50).Select
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
Selection.ShapeRange.Line.Visible = msoFalse
Case Is >= 4
ActiveSheet.Shapes.AddShape(msoShapeOval, 20, 20, 50, 50).Select
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5
Selection.ShapeRange.Line.Visible = msoFalse
Case Else
ActiveSheet.Shapes.AddShape(msoShapeOval, 20, 20, 50, 50).Select
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Line.Visible = msoFalse
End Select

End Sub