Shape fill not changing when linked cell changes

[Ochimus]

SITE worksheet on attached lists 11 properties in Col C.
Col F has DropLists where user selects which "stage" the work is on that property.

MAP sheet has outline of the street with shapes showing the locations of each property in the street.

Code is set to run if any cell in F5:F15 changes.
First part should link each shape to it's matching row in the SITE worksheet, (e.g. IF you change F5 then row 5 is the Active row, so Oval 5 becomes the "active" shape).
Second part should then change the Fill colour depending on which stage is selected in Col F on the SITE worksheet (e.g. If F5 is "Step 3" then Oval 5 should turn to Yellow. If F5 changes to "Step 10") then Oval 5 should turn to Chrome.

Tried setting the "active" shape as "SObject" so the "fill" element only has to run once, but throws "RunTime Error 91" - Object Variable or With block variable not set.

Please Login or Register  to view this content.

All solutions, suggestions or alternatives welcome as ever.

Ochimus

[Greg M]

Hi there,

Tried setting the "active" shape as "SObject" so the "fill" element only has to run once, but throws "RunTime Error 91" - Object Variable or With block variable not set.

Your code tries to "assign" a value to the SObject Shape, but you need to "set" its value.


See if the following version of your code gets things moving in the right direction:

Please Login or Register  to view this content.

Unintuitive as it seems, setting the ForeColor (rather than BackColor) causes the required change.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Ochimus]

Greg,

Definitely a star!

Two lines replacing half a forest in my attempt, and works seamlessly!

Many thanks and can now mark this as Solved.

Ochimus

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M

[Sintek]

Ochimus...I know this is solved...For future reference though...As you have all the information already laid out...

Please Login or Register  to view this content.