Change the background color of a shape based on the value of a cell

[domz3669]

Hi,

Im want to create a mapping of our resort which has around 140 villas. So i will create 140 rectangles representing rooms. I will simply refer their room numbers to a list. The room number list is in Column A, while column B is the room status. Room Status can be OC,OD,VC,VCI,VD, and OOO and each status has its own background color:

OC = Blue
OD = Blue
VC = Dark Green
VCI = LightGreen
VD = Brown
OOO = Red

Now, I want that If A1 = 1021 and B1 = VC (green) then the 1021 shape will also change to green (copy the background color of the status of 1021).

Please see attachment for a sample.
Thank You!!

[humdingaling]

1.
why arent you using conditional formatting to color your cells in the list


2.
do you really need shapes?
if yes, you should probably name the shapes something meaningful like the room number instead of Rectangle 1, Rectangle 2, etc

[domz3669]

Hi humdingaling,

The sheet uploaded was just a demo to show the positions, I can use conditional formatting ofcourse, but my main goal is that a shape must mimic the color of the referring cell(whatever the color maybe).

I need shapes since I need to actually copy the rooms positioning in the map as well as their actual shape and distance to each other. Naming the shapes should be easy but I really need its Fill Color to copy the reference cell background.

[domz3669]

I updated the sample sheet. I already used conditional formatting, now my only problem is, the shape with the assigned room number must follow the color of that cell.

[humdingaling]

ok
try this out then

the only thing is you need to name your shapes the ROOM number
and as long as the shape name length is between 3 and 5
it will automatically update


i added another piece of code that will help insert the text in the shape based on the shape name

Please Login or Register  to view this content.

[humdingaling]

ps if you dont want to use conditional format then the syntax would be

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

[domz3669]

Thank you for the code Humdingaling,

I tried it on my computer, Im out of work so now I'm using Excel 2016. Your code works perfectly as I want to. I will try it tomorrow in our office using Excel 2007. Hopefully, it will work.

Thank you so much!
I will update tomorrow.

[domz3669]

Hi Humdingaling,

So i tried it in Excel 2007, run the macro and came an error "Object doesnt support this property and method" Run-time error "438".
When I try to debug, this line was highlighted in yellow:

Please Login or Register  to view this content.

So i removed the .DisplayFormat, but then the shapes wont follow the color of the cell, instead it shows white.

[humdingaling]

gah forgot displayformats doesnt work on 2007

Please Login or Register  to view this content.

change the code to above instead
it manually does the selecting of color in the code itself
change RGB to what suits you
http://dmcritchie.mvps.org/excel/colors.htm
This is what i used as resource for RGB numbers

alternatively you can go back to manually coloring in the cells and using this line instead

Please Login or Register  to view this content.

[domz3669]

Thank You Hum,

Now it works perfectly. Here is my sample:

Sample Output.png

Thank you... Till next time!

[humdingaling]

glad to see it working

thanks for the rep