change the shape of cell?

[04517661]

Is it possilbe to change the shape of a cell to a circle?Or is there a way you can make a shape act the same as one of your cells?


Thanks.

 

[random_noise]

You cant change the shape of a cell and you cannot use an auto shape in the same way as a cell. What is it you are trying to achieve? It may be possible but you'll need to go into a little more detail.

 

[04517661]

im trying to get a traffic lights so that when I enter in a number greater than o in certain columns then it changes the cell color.I have this working using conditional formatting but i dont think it looks that good when its not a circle.

 

[Peter_SSs]

Is it possilbe to change the shape of a cell to a circle?Or is there a way you can make a shape act the same as one of your cells?


Thanks.

First question was answered in your earlier thread here:

http://www.mrexcel.com/forum/showpost.php?p=1596423&postcount=10

Re the second question: Was the Tom Urtis suggestion in the link from the above post no use?

 

[Sneech#7]

You might put the value into a shape instead of a cell. Then you could use something like

ActiveSheet.Shapes("Oval 1").Select
Selection.Characters.Text = yourValue
If .....your test here
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
elseif...

 

[RoryA]

You can use code like this to add, or change the colour of, circles to a worksheet based on cell values (here if the value in A is less than 5, the circle is red; if the value equals 5, the circle is yellow' otherwise it's green):

Sub ColourCircles()
    Dim wks As Worksheet
    Dim rngData As Range, rngCell As Range
    Dim shp As Shape
    Dim dblTarget
    
    ' change target value as required
    dblTarget = 5
    Set wks = ActiveSheet
    With wks
      Set rngData = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
      On Error Resume Next
      For Each rngCell In rngData
         Set shp = .Shapes("Circle" & rngCell.address(0, 0))
         If shp Is Nothing Then
            Set shp = .Shapes.AddShape(msoShapeOval, rngCell.Offset(0, 1).Left + 1, _
                           rngCell.Top, rngCell.Height, rngCell.Height)
            shp.Name = "Circle" & rngCell.address(0, 0)
         End If
         Select Case rngCell.Value
            Case Is < dblTarget
               shp.Fill.ForeColor.rgB = vbRed
            Case dblTarget
               shp.Fill.ForeColor.rgB = vbYellow
            Case Is > dblTarget
               shp.Fill.ForeColor.rgB = vbGreen
            Case Else
               ' probably an error
               shp.Fill.ForeColor.rgB = 0
         End Select
         Set shp = Nothing
      Next rngCell
   End With
End Sub

FWIW.

 

[04517661]

This was my question from another thread:

I want to turn a certain cell(sheet1!K2) either green, orange or red depending on certain conditions.There are 3 conditions.If 3 of these conditions are met I want the cell to go red.If two of these are met I want it to also go red.If one of these conditions are met I want it to go orange and if 0 are met I want it to go green.

The conditions are :
1) if a number > 0 is entered into B11:H11
2) if a number > 0 is entered into D17:H17
3) if Ex is entered into cells sheet2!B12:Y27


And this is the solution I got:

1. Colour the cell green with ordinary formatting (not Conditional Formatting)

2. On Sheet2 give the range D2:D16 a name (I used Sh2ColD). One way to do this is select D2:D16 on Sheet2, then type the name in the Name box (white area just above Col A label).

3. Apply the 2 Conditional Formatting conditions as shown below.

On my sample K2 is red because Sheet2 D2:D16 does contain "Ex" so 2 of your conditions are met.

Conditional Formatting

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 40px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>K2</TD><TD>1. / Formula is =(MAX(B11:H11)>0)+(MAX(D16:H16)>0)+(ISNUMBER(MATCH("Ex",Sh2ColD,0)))>1</TD><TD style="BACKGROUND-COLOR: #ff0000">Abc</TD></TR><TR><TD>K2</TD><TD>2. / Formula is =(MAX(B11:H11)>0)+(MAX(D16:H16)>0)+(ISNUMBER(MATCH("Ex",Sh2ColD,0)))=1</TD><TD style="BACKGROUND-COLOR: #ff9900">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


When you tried this, Excel should have basically told you one reason why it didn't work with its message along the lines of "You cannot use references to other worksheets in Conditional Formatting".

The other reason it would not work is that you are now dealing with a two-dimensional range on Sheet2 whereas it was originally just a single-column range.

I have named B12:Y27 on Sheet2 as Sheet2Range and then used this Conditional Formatting. I think it works how you want.

Conditional Formatting

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 40px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ff0000"> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>K2</TD><TD>1. / Formula is =(MAX(B11:H11)>0)+(MAX(D16:H16)>0)+(SUMPRODUCT((Sheet2Range="Ex")*ROW(Sheet2Range))>0)>1</TD><TD style="BACKGROUND-COLOR: #ff0000">Abc</TD></TR><TR><TD>K2</TD><TD>2. / Formula is =(MAX(B11:H11)>0)+(MAX(D16:H16)>0)+(SUMPRODUCT((Sheet2Range="Ex")*ROW(Sheet2Range))>0)=1</TD><TD style="BACKGROUND-COLOR: #ff9900">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
This worked(thank Peter) but I now want think to work for a shape(circle) instead of a cell.

I think il need to use code instead of formulas.
Does anyone know how to do this or do they know of any think similar I can to to achieve what I want.

Thanks


P.S(To peter, thanks for your help, the tom urtis thread wasnt quite what I wanted but It did help for something else)

 

[Legacy 98055]

You can use formulas. I'll try and explain but I reccommend that you download the example...

<a href="http://home.fuse.net/tstom/0623081416.326223.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0623081416.326223.zip">0623081416.326223.zip</a>

You can use pictures or any other type autoshape. This example uses pictures.

1. Place any picture or autoshape in cell K2. This is the traffic light indicator you mentioned earlier.
2. Choose insert, name, define Name:="ConditionCount", RefersTo: =COUNTIF(Sheet1!$B$11:$H$11,">0")+COUNTIF(Sheet1!$D$17:$H$17,">0")+COUNTIF(Sheet2!$B$12:$Y$27,"=Ex")
3. Choose insert, name, define Name:="ChoosePic", RefersTo: =IF(ConditionCount=0,INDIRECT("Sheet3!A1"),IF(ConditionCount=1,INDIRECT("Sheet3!B1"),INDIRECT("Sheet3!C1")))
4. Sheet3A1 contains your greenlight, Sheet3B1(yellow/orange light), Sheet3C1(red light)
5. Select your shape in K2. In the formula bar, "=ChoosePic"

The ChoosePic name will evaluate to a reference and update your shape accordingly. Download the example above...

 

[04517661]

Thanks very much for your help "Right Click"
It almost works perfectly now, there is just one thing I need to change.
At the moment the light changes every time you enter a no. > than 0 in any of the yellow colored cells.What I would like is for it to only change to red if a no. > 0 is entered in 2 or more of the conditions and not if it is entered in more than once in the same conditions.

e.g if 1 is entered into cell D17 then the light goes orange and if 1 is entered into E17 then the light goes red, This is not what i want to happen, I want the light to stay orange because only one condition is met, (There could be ones from D17 to H17 and I still want it to be orange)
I only want the light to go red if say 1 is entered in cell D17 & B11.because then two conditions are met.

Sorry its hard to explain but I hope you understand what Im talking about.
Do you know how to do this?

Thanks.

 

[Legacy 98055]

Choose insert, name, define Name:="ConditionCount", RefersTo: =IF(COUNTIF(Sheet1!$B$11:$H$11,">0"),1,0)+IF(COUNTIF(Sheet1!$D$17:$H$17,">0"),1,0)+IF(COUNTIF(Sheet2!$B$12:$Y$27,"=Ex"),1,0)

This will segregate your conditions into ranges of cell as opposed to individual cells...