Change Shape Fill colour based on cells values.

Hi there everyone,

I wonder if you can help me here.

I need to change the Shape Fill color based on cells drop down menu value, this value is "Yes" and "No"

I have attached a sample file to this thread.

What I need is:

- If cells are empty, I want the Shape color as red.
- If one of the cells value is "No" or one of the cells is empty, I want the Shape color as yellow
- If all cells value is "Yes", I want the Shape color green.

I'm sorry, I am very new with VBA and I've no idea about the code for this one.

You assistance on this matter will be highly appreciated.

Many thanks in advance and kind regards,
Filipe

Hi,

try and copy this into sheet1 module.

Please Login or Register  to view this content.

Thanks a million for that RHCPgergo.

That's the code I was looking for.

This is only a sample as I need to do something similar but with lots of Shapes.

Can you please confirm if for each shape I need to put the same code and only change the line where the shape name is as following:

Please Login or Register  to view this content.

And just one more thing, where can I find each color number? for example, red is 2; green is 3...

Many thanks in advance and kind regards,
Filipe

I'm glad it works!

Here is the adjusted one. Please make sure that each Oval is named properly. (They have to be named like Oval2, Oval3, etc. Each number representing the row of the data it belongs to. Don't use space. "Oval 2" is wrong.) I added a macro that names the Ovals if you run it, but I didn't test it. Read the comments in the code for extra info. (Alt+F11 to open VBA in Excel.)

Perfect, thanks for the very useful tip, really appreciated.

The other thing I need to know is the color numbers. I've seen few ColorIndex in Internet but the numbers you have maintained on my example, does not matches the ones I saw.

Can I please ask you where can I find a color index for the code you have provided? I need other color for my project, such as Black; Grey, etc.

Many thanks in advance and kind regards,
Filipe.

Thanks a million for all users involved.

I really appreciate your fast and amazing reply to my query.

Best regards,
Filipe

I am just wondering if you have tried any of these.
I have not used them myself but it would be great to know if and how good they work.

Good luck Filipe

Yes Jolivanes,

They work perfectly and you can see the different colors between ColorIndex and SchemeColor.

That's really a brilliant code.

Thanks a million for this.

BR,
Filipe

Hello, I just joined this sight and am very new to VGA. I am currently in the process of trying to understand the function. I am in request of a code in this same manner. the code I am looking for function would take the value from the cells in the "Z" column and turn the specific oval either blue, red, yellow, or green. I tried to use the 1 "code fix", but I had no luck. I was unable to download the second excel format to look.

any help or a point in the correct direction would help

Thank you