Circling cells based on a value
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
|