AutoShape Condition

[jbfrank]

I have a cell that I would like to populate with a colored oval. There are only 3 colors I need (Red, Yellow, Green).

I would like cell A1 to populate with a Red Oval if the value in cell A2 is "R", a Yellow Oval if the value in A2 is "Y" and a Green Oval if the value in cell A2 is "G".

Any ideas?

Thanks in advance for any help.

 

[chiello]

Try the following code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddShapeInA1()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> MyShape <SPAN style="color:#00007F">As</SPAN> Shape
<SPAN style="color:#00007F">Dim</SPAN> ColIdx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("A2")
    <SPAN style="color:#00007F">Case</SPAN> "R"
        ColIdx = 10
    <SPAN style="color:#00007F">Case</SPAN> "Y"
        ColIdx = 13
    <SPAN style="color:#00007F">Case</SPAN> "G"
        ColIdx = 11
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

<SPAN style="color:#00007F">Set</SPAN> MyShape = ActiveSheet.Shapes.AddShape(msoShapeOval, 1.5, 0.75, 42.75, 10.5)
<SPAN style="color:#00007F">With</SPAN> MyShape
    .TopLeftCell = Range("A1")
    .Fill.ForeColor.SchemeColor = ColIdx
    .Fill.Visible = msoTrue
    .Fill.Solid
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> Macro2()</FONT>


Post for feedback

Ciao

 

[jbfrank]

This works great! But what if I actually want the oval to appear in another cell? I tried changing the cell value in the code, but no matter what, the oval always shows up in cell A1.

 

[Juan Pablo González]

These numbers

1.5, 0.75, 42.75, 10.5

are the left, top, width and height coordinates where the shape will appear. You could use

Range("G2").Left, Range("G2").Top, 42.75, 10.5

for example, to make it appear in G2.