• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

fill color of shape based on value

hello,
i'm trying to figure out if it is possible to do this:
- cell with a dropdownlist of 'red, green 'amber'
- a shape on another sheets gets the color from the dropdownlist
so fill color is green when 'green' is selected from the dropdownlist

Does anyone has an example worksheet for this?
thanks!
W
 
Hi Wim ,

Can you accept if this is done using VBA ? If so , it is quite easy.

If VBA cannot be used , then is your shape an irregular shape or is a rectangle ? If so , what can be done is to make the shape colorless , and transparent , and color the cells behind the shape based on the drop-down selection.

Narayan
 
Hi Wim ,

The code is not readily available , but can be written ; for this purpose , you will need to specify whether the number of options is only 3 , and these options are what you have already posted viz. Red , Green and Amber , or are they some other text values ?

Narayan
 
Hello,

Is it possible to let this work if I put a formula instead of the data validation list.

I've put an example in attachment.

I want the shape to be updated if i change the cell in another sheet. It works only when I press enter in the cell of sheet 4, it doesn't do it automatically

Thanks!
Wim
 

Attachments

  • Example_Wim.xlsm
    16.7 KB · Views: 195
Hi Wim ,

The earlier workbook made use of the Worksheet_Change event procedure ; this is triggered only when a user changes the contents of a cell , not when a formula does it. So if you want , the colour of the shape can be changed when the drop-down on Sheet1 is changed , but not when the contents of G1 on Sheet4 change ; of course there is a work-around ; if you are on Sheet1 , and you change the drop-down selection , you will need to activate Sheet4 to see the shape ; this activation of the sheet can trigger a macro , which can then look at G1 on the sheet , and change the colour of the shape to suit the text.

Narayan
 
Hello SirJB7,

That's what even I thought, but we have got a lot of pro people in excel so was thinking if by any chance we have an option in excel of via UDF...;)
 
Hi Wim ,

Can you check this file ?

Narayan
Hi, Abhijeet R. Joshi!
You clearly said "without using VBA" and my comment was related to that.
Now you say "have an option in excel via UDF": User Defined Functions are made of VBA code, but functions neither native nor user paint shapes. Sub procedures do. And you yet have the 7th post from NARAYANK991, which I quoted.
Regards!
 
Hi,
How about this: create a cell with conditional formating that changes color depending on the value of a cell. Then copy the cell with the format and paste as a linked image. Warp the image and/or add others to create the desired shape or change its colors...
 

Attachments

  • Excel shape change based on cell content.xlsx
    16.7 KB · Views: 187
Here is my shot with formula....there is a much better way of doing it...if i use form controls...
 

Attachments

  • shapecolorwithformula.xlsm
    11.6 KB · Views: 225
@RmarkR : Here you go...this is what i guess you are looking for....

and thanks for the link..it is nice
 

Attachments

  • Solution2.xlsm
    21.9 KB · Views: 173
Im having a similar problem. The Example_Wim.xlsm is perfect.

NARAYANK991
BUT I want to have multiple objects which are changed depending what is said in different cells. All different colours (i want to create multiple traffic lights)

Say:
"Oval 1" is dependent on what is written in A1
"Oval 2" is dependent on what is written in A2
"Oval 3" is dependent on what is written in A3

How do I link the VB code up for multiple objects??
 
Hi Wim ,

Im having a similar problem. The Example_Wim.xlsm is perfect.

NARAYANK991
BUT I want to have multiple objects which are changed depending what is said in different cells. All different colours (i want to create multiple traffic lights)

Say:
"Oval 1" is dependent on what is written in A1
"Oval 2" is dependent on what is written in A2
"Oval 3" is dependent on what is written in A3

How do I link the VB code up for multiple objects??

Narayan
 
Hi ,

Since this involves code which is linked to objects , can you please upload your workbook with the data and the objects in it ?

Narayan
 
Hi ,

Thanks for the response.

The original code for this that I believe you wrote "Narayan" created allowed you to change the color of "oval 2".

I need 4 different Ovals to be independently changed (to create a traffic light system in a report). How can I add the same code, changing the values of the

"G1" which changes "Oval 2",
and add in :
"G2" changes "Oval 3"
"G3" changes "Oval 4"
"G4" changes "Oval 5"

Ive attached my sheet.
Thanks so much in advance!
 

Attachments

  • Example_Wim DP request.xlsm
    16.4 KB · Views: 85
Back
Top