Query change the Shapes color by entering value in cell

Hello,

if you want to do this with shapes you will need code. There is a way to do this without code if you use cells and conditional formatting. You may need to re-arrange your data entry cells.

See attached file.

Shape_ColoringTest.xlsm

Exaclty - Much simpler with CF in cells -

Code (I am new to VBA) but I would if tasked with this...do it like this.

On the worksheet that this lives so Sheet 2 for you, the following would be entered

Probably a more efficient way to do this - but basically I am checking to see if there was a change to any of your cells

Please Login or Register  to view this content.

Next in a Module we need to add an Update sub -

Again, probably a clean loop method that could be applied, I am not there yet... but it checks if the related cell is a 1 if so make the corresponding (Now named) Shape Yellow, otherwise make sure it is white. Runs through all of the cells and updates the colors. It will always jump to A11 after updating a cell as this is the last range I select just to deselect the shapes in the process -

Please Login or Register  to view this content.

So again, probably a more efficient way to code that, but there you go - and as Teylyn pointed out... CF is going to be much simpler in the end -

I am still new with the VBA - I would end this thread (Mark as solved) then take the above code and ask in the VBA/Programming section for a way to loop that and explain that this is a sample of what you are doing, the amount of shapes is actually much more than that in which you see here. Also give them the name format of the shapes in your book -

TO mark it as solved go to thread tools near the top of this thread.

Good luck!

You can shorten the change event to just:

Please Login or Register  to view this content.