Circling cells based on a value

[ezem1y]

What I'm trying to do is circle cells(J14 and R14) based on thier
value(empty cell no circle, occupied cell circled). I can only get one
circle to appear not two, and to make the circle dissappear I have to
manually run another macro. This is what I have so far, and is it
possible to all the code on the active sheet only?

for the workbook I have:

Sub Hide_It()
ActiveSheet.Shapes("Oval 2").Visible = False
ActiveSheet.Shapes("Oval 6").Visible = False
End Sub

and for the Active sheet I have:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
With Range("j14")
If .Value > 0 Then
ActiveSheet.Shapes("Oval 2").Visible = True
End If
End With
End Sub

any help would be greatful, thanks

 

[Allllen]

Assuming that oval 2 is the one around j14 and oval 6 is the one around r14:

1 completely get rid of sub Hide_It()
2 not sure that you really want that target.count >1 question in there
3 use this (it only works on the active sheet anyway)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$14" Then
If Range("j14") = "" Then ActiveSheet.Shapes("Oval 2").Visible = False
Else ActiveSheet.Shapes("Oval 2").Visible = True
End If

If Target.Address = "$R$14" Then
If Range("r14") = "" Then ActiveSheet.Shapes("Oval 6").Visible = False
Else ActiveSheet.Shapes("Oval 6").Visible = True
End If

End Sub

Please give me a green tick if this is a correct answer for you.

 

[ezem1y]

You were correct on what I'm trying to do, but it still doesn't work.
If J13 is the sum of c13-i13 and r13 is the sum of k13-q13, I end up
with a zero in J&R13 so I replaced your """ with 0. But the ovals don't
go away when I have a "0", they only way the ovals hide is to remove
the sum from these cells, but the oval swaps back and forth between the
two cells.

 

[ezem1y]

 

[Ron Coderre]

See if this no-vba method works for you.

Basic procedure:
1)Draw the oval shape around cell J14 and set the fill property to transparent

2)Move that shape and to a blank area of the workbook
(I'll assume AA1 and that cells AA1:AB3 enclose it)
(I'll also assume that cell Z1 is blank)

3)Create this Dynamic Range Name
Names in workbook: picOval_J14
Refers to: =IF(ISBLANK($J$14),$Z$1,$AA$1:$AB$3)

4)Copy cell J14
5)Hold down the [shift] key and, from the Excel menu: <edit><paste picture>

While that picture is still selected...
6)In the formula bar enter: =picOval_J14, then press [enter]

Now...
if there is a value in J14 the image will display the oval in AA1:AB3
If J14 is blank the image will display the Z1 (the blank cell)
Note: for best results, turn off grid lines

Fr cell R14, repeat steps 3 thru 6 (referring to R14, instead of J14)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP