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

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.

--
Allllen


" wrote:

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

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.



Allllen wrote:
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.

--
Allllen


" wrote:

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 wrote:
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.

--
Allllen


" wrote:

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

See if this no-vba method works for you.

Basic procedu
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


" wrote:

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