Colour an object based on cell content

[JonesyUK]

Hi - Thanks for your advice.

I have a circle object on an Excel spreadsheet(I used "Insert", "Shapes" to put it there).

I would like to change the colour of the circle object based on the contents of cell A1.

Cell A1 will be "Red", "Yellow" or "Green".

If I type "Green" into cell A1, then I would like the circle object to change to green.
If I type "Yellow" into cell A1, then I would like the circle object to change to yellow....

etc. etc.

Hope this makes sense, and again, thanks for your response.
C

 

[JonesyUK]

Hi Andrew, I'm not having any luck with this exercise... in fact, I can't even get the objects on the graphs to rename properly...

I've placed the file in the following area:

https://www.filesanywhere.com/
username: excelfiles
p/w: test1234

filename = KBI 3 Month Trend_line_P12toP2_Line_2.xlsm

please right-click the file and download... maybe this will give a better idea...

Thanks,
Clint

 

[Andrew Poulsom]

I understand what you are trying to do, but you don't have any of the shape names in column M. And it's not easy when the shapes are on a chart. First you have to reference the chart, then the shape, like this:

Sub Test()
    Dim ChObj As ChartObject
    Dim Sh As Shape
    For Each ChObj In ActiveSheet.ChartObjects
        For Each Sh In ChObj.Chart.Shapes
            MsgBox Sh.Name
        Next Sh
    Next ChObj
End Sub

So you would have to store the name of the Chart Object as well as the name of the Shape.

 

[JonesyUK]

Thanks Andrew.

Is this something you can complete and bill me for? Please let me know cost...

Or do you know of someone who can turn it around pretty quickly?

Thanks,
Clint

 

[Andrew Poulsom]

I'm just a volunteer. There are plenty of consultants on Google, some of which frequent MrExcel.

 

[JonesyUK]

Hi Andrew, ok, so I've got this working.

However, adding new charts and shapes is not working. When I copy and paste a chart that works, it does not allow me to rename the chart or the object(in the named range area). How do I do this please? Thanks,

 

[Andrew Poulsom]

You can rename a chart by typing something like this in the Immediate window in the VBE:

ActiveChart.Parent.Name = "Whatever"

and pressing Enter. For a selected shape:

Selection.Name = "MyShape"

 

[eyong123]

Hi Andrew, I have tried to use the code you proposed to adapt it to my needs to no avail. I will greatly appreciate if you can help

I have 27 Rounded Rectangle all of which are assigned macros to fill in 1 to 27 respectively into cell B12.

Only one of these can be selected at any given time.

How can I proceded with a macro that change the colour of a rectangle from its initial yellow to green to indicate that the button ha been selected.

When a different shape is selected the previous one should return to its intial yelow colour while the new shape changes to to green


many Thanks in advance

 

[Andrew Poulsom]

What are the names of your shapes? Please post your VBA code.

 

[S0x24]

Hi Andrew, =) (Windows XP, Excel 2010)

I'm trying to make the first code you posted on this thread work for me in a different way, but I can't seem to get it to work. I've been struggling for awhile to figure it out. By the way, I just discovered VBA a few days ago and I have a hunch that I don't know what I'm doing.

Okay, so here is what I have...

- 3 sheets named "A", "B" and "L" (w/o the quotation marks)
- The object "Box" is on sheet A.
- A drop down list assigned to cell A1 on sheet "B". The VBA CODE is also designated to sheet "B"
- Sheet "L" contains the source list for the drop down list.

Here is what I'm trying to do...

- I choose from one of 4 values on the drop down list from cell A1 on sheet "B" and the object "Box" on sheet "A" changes color based on the what was chosen.

Below is your code that I slightly Frankenstein'd:

CODE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
With Shapes("Box").Fill
Select Case Target.Value
Case "Action"
.Visible = msoTrue
.ForeColor.SchemeColor = 10
Case "Caution"
.Visible = msoTrue
.ForeColor.RGB = RGB(254, 134, 1)
Case "Monitor"
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
Case "Normal"
.Visible = msoTrue
.ForeColor.SchemeColor = 17
Case Else
.Visible = msoFalse
End Select
End With
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Andrew Poulsom]

Are you getting an error? If so maybe add the text in red below:

With Worksheets("A").Shapes("Box").Fill