Copying image to cell - conditionally

[Mike]

Hi. I am using Excel 2002 (XP).

I would like to accomplish the following for some cells.

If the cell is below a certain value, the cell value is
CIRCLED in green.
If the cell value is above a certain value, the cell
value is BOXED in red.

I can achieve the boxing in red by using conditional
formatting of the cell borders, but I cannot figure out
how to accomplish the circle.

Does anyone have any ideas?

Thanks,
Mike.

 

[J.E. McGimpsey]

One way:

put this in your worksheet code module:

Private Sub Worksheet_Calculate()
Const CERTAIN_VALUE = 100
Dim cell As Range
Dim shp As Shape

On Error Resume Next
For Each cell In Range("A1,B4,C10,G12")
With cell
Me.Shapes("Circle" & .Address(False, False)).Delete
If .Value > CERTAIN_VALUE Then
With Me.Shapes.AddShape(msoShapeOval, _
.Left, .Top, .EntireColumn.Width, _
.EntireRow.Height)
.Name = "Circle" & cell.Address(False, False)
.Fill.Visible = msoFalse
With .Line
.Weight = 2
.DashStyle = msoLineSolid
.Style = msoLineSingle
.ForeColor.SchemeColor = 17
.Visible = True
End With
End With
End If
End With
Next cell
End Sub

adjust your range as necessary