Change shape fill color depending on cell value

[xe-dingo]

Hi guys,

(newbie vba)
I've been searching the web for the following issue (I just cannot seem to get it to work):

I've got two shapes in which I want the color to change depending on a drop down list Open/closed.

If valve_1 has status "Open" then that corresponding valve is to be filled with green.
If it is "Closed" it is to be red.
The same for Valve 2.

I have uploaded what I've got so far on the basis of an example I have found online, but it just does not work.

Any help much appreciated.

[cytop]

There was no code in that sample to work... So, simplistic code. You may wish to add extra checks to determine if just the dropdown changes.

Please Login or Register  to view this content.

[Andy Pope]

there was code, although incorrect, in the thisworkbook object.

moved to the sheet object and using the change event

Please Login or Register  to view this content.

the code you had will work

[xe-dingo]

reference to cytop answer:

Awesome, it works, one thing though.
How did you know to put "3" and "5" as the reference to those shapes? When I select them, "valve_1" and "valve_2" appear.
I've added a new third valve, but how do I find the shape name to put in to the code?

Update: just since his code was more simple

[Andy Pope]

Alternative using a UDF

code in standard code module

Please Login or Register  to view this content.

in a cell use formula

=udfVALVECOLOR("Valve_1",IF(L9="OPEN",65280,255))

65280 is RGB(0,255,0)

[cytop]

there was code...

So there is - I double clicked on the ThisWorkBook module for another workbook - Duh!

As far as the shape names go, you can simply use

Please Login or Register  to view this content.

I just hacked some code together quickly after assuming there was none...

[xe-dingo]

Hi guys,
Thanks so much for the help.
At the moment I am using the following code which is working perfectly:

Please Login or Register  to view this content.

Only thing is, that I would now like to have three color options instead of just two. OPEN=Green, CLOSED=Red and NA=White.
How can this be incorporated in to the code? any help MUCH appreciated.