Circle value in cell

[Mike.]

Hi.

I have a spreadsheet that has values in cells C26 through
Y36. If the value is greater than the value in A1, I
need it to be boxed in red. If the value is less than
the value in A2, it needs to be CIRCLED in green.

I can accomplish the red boxes by using conditional
formatting. Can anyone tell me how to accomplish the
green circles?

Thanks,
Mike.

p.s. I am using Excel 2002

 

[J.E. McGimpsey]

one way:

Put this in your worksheet code module (right-click on the worksheet
tab and choose View Code):

Private Sub Worksheet_Calculate()
Dim cell As Range
Dim shp As Shape
On Error Resume Next
For Each cell In Range("C26:Y26")
With cell
Set shp = Shapes("Circle " & .Address(False, False))
If shp Is Nothing Then
shp.Visible = .Value < Range("A2").Value
With ActiveSheet.Shapes.AddShape( _
msoShapeOval, .Left, .Top, .Width, .Height)
.Name = "Circle " & .Address(False, False)
.Fill.Visible = msoFalse
.Line.Weight = 1.25
.Line.ForeColor.SchemeColor = 17
.Line.Visible = msoTrue
End With
End If
shp.Visible = .Value < Range("A2").Value
End With
Next cell
End Sub



this assumes that C26:Y26 are calculated. If they are entered
manually, use the Worksheet_Change() event instead.

 

[Dave Peterson]

Is a macro ok?

Option Explicit
Sub testme()

'c6:y36
Dim myRange As Range
Dim myCell As Range
Dim myKeyValue As Variant
Dim myOval As Shape

With Worksheets("Sheet1")
myKeyValue = .Range("a2").Value
Set myRange = .Range("c26:y36")
For Each myOval In .Shapes
With myOval
If .AutoShapeType = msoShapeOval Then
If Intersect(.TopLeftCell, myRange) Is Nothing Then
'do nothing
Else
.Delete
End If
End If
End With
Next myOval
End With

For Each myCell In myRange.Cells
With myCell
If .Value < myKeyValue Then
Set myOval = .Parent.Shapes.AddShape(Type:=msoShapeOval, _
Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height)
myOval.Fill.ForeColor.SchemeColor = 42
myOval.Fill.Transparency = 0.5
End If
End With
Next myCell

End Sub

With the values centered in C26:Y36, it didn't look too bad.

It removes all the ovals in C26:Y36 and then reapplies them if required.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ps. that .schemecolor looked green on my pc. You may want to record a macro
when you change it to the green that you like. Then steal that number and put
it in the code.