• 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

Hi ,

You are a complete star!! looking at it, I was going down this path but it would have taken me a long time! Thanks so much!!!
 
Hi ,

See if this is OK.

Narayan

Hi
I am new to vba,I am trying to create a dashboard based on Actual vs Target (See attached) I am trying to use your method but its not working can you please help.

Regards

Sohil
 

Attachments

  • Example_Wim DP request (1).xlsm
    20.4 KB · Views: 26
Hi Sohil ,

See your file now.

Data is to be changed in the Actual matrix , in the range K3 through M5.

Narayan
 

Attachments

  • Example_Wim DP request (1).xlsm
    20.9 KB · Views: 31
Hi Narayan

I have added one more column,So the data in the range K3 through N5 will change each month.

Regards
Sohil
 

Attachments

  • Example_Wim DP request v2.xlsm
    21.4 KB · Views: 21
Hi Sohil ,

Will there be any more changes ? Surely this is a matter you could have mentioned in your earlier post ?

Narayan
 
hi guys,

I have a similar issue and have been able to resolve it through vba. However, I need to find a way of doing so without code.

I have 4 columns(deadline,actual date,status and Shape). The status column is a formula which looks at deadline and actual date to return a status(complete,overdue, in progress etc). The shape then changes colour depending on the status.

Any help would be appreciated.

Thanks!
 

Attachments

  • test1.xlsx
    9.5 KB · Views: 18
Just to add i can do it so that when the macro is run it works. However, is there a way that the color changes as soon as the value is entered...e.g. if the status becomes overdue then the color automatically becomes red rather than having to run the macro each time.
 
Hi Sohil ,

See the file now.

Narayan

Hello Narayan and Everyone,

I have a attached the Excel file I have done the conditional formatting for Cells to Get the Red, Amber, Green and Blue. But I Required to Display those colors in Shapes that I have placed in File.

Is there any way to get this done??
 

Attachments

  • ColorButtons_PW1_Final_For One Process.xlsx
    282.7 KB · Views: 26
Hi ,

See if this is OK.

Narayan

Narayan thank you,

The Shapes colors are updating according to the condition and this is what i required. But one thing is missing Can i get the value to be seen in the shape itself. I mean the value that is there on the corresponding day.

if it can be done it would be helpfull.

Sorry last one addition, i just want to show or highlight comments by dept only when person requires to see it. so by using Check box can we link that.

Sorry i am really disturbing a lot.

Thanks a ton. :)
 

Attachments

  • ColorButtons_PW1_Final_For One Process.xlsm
    319.3 KB · Views: 17
Narayan, Could you please guide me some basic ways to start learning the Vb for excel.

I tried to understand the code by going through it but certain keyword like "Layer 1", Range("Check Cells") , CurrShape = "Oval " & Layer1, NextShape = "Oval " & Layer2
and Layer1 = Layer1 + 1, Layer2 = Layer2 + 1
Unable to understand these keywords.
Could you please give some hints to understand and so that i can grow this dashboard for other processes

Thank you.
 
Hi Narayan,
I have the same problem. I have lists of panels with several shape forms in "Sheet 1" and its panel numbers and status in "Sheet 2".
May I know how to put color on shapes on "Sheet 1" based on text values given on column B "Sheet 2". That is
if Column B= "Approved", then color green
if Column B= "Submitted", then color red
if Column B= "Commented", then pink
I attached my spreadsheet so you can have a better understanding.
Thanks.
 

Attachments

  • DWall Progress Report.xlsm
    48.7 KB · Views: 17
Hi ,

The entire construction in Sheet1 is through shapes which are freeform shapes ; the table in Sheet2 has shapes labelled S001 , S002 , S003 ,...

How are we to relate these labels to the freeform constructs in Sheet1 ?

If anything has to be done , it can be done only if the freeform constructs in Sheet1 are relabelled to S001 , S002 , S003 ,...

Narayan
 
Hi ,

The entire construction in Sheet1 is through shapes which are freeform shapes ; the table in Sheet2 has shapes labelled S001 , S002 , S003 ,...

How are we to relate these labels to the freeform constructs in Sheet1 ?

If anything has to be done , it can be done only if the freeform constructs in Sheet1 are relabelled to S001 , S002 , S003 ,...

Narayan


Hi Narayan,

Let's say for example I changed all the freeform shapes to S 001, S002, S 003. How am I going to change the color based on column B. I managed to do it but it is referenced based on "Sheet 1", and I want it based on "Sheet 2". And also is it possible to update the panel numbering text in the chart (S 001, S002, S 003) based on the column A of "Sheet 2"?
Thanks Narayan :)
 
Hi ,

What ever has been done as far as naming the shapes is concerned , needs to be redone !

The shape name is available in the Name bar , not the Formula bar ; I have changed the names of the first four shapes ; the remaining need to be done.

I have assigned 3 colours to the 3 status descriptions of Approved , Submitted and Commented. If there are going to be more such status descriptions , please list them along with their colours.

Narayan
 

Attachments

  • DWall Progress Report.xlsm
    56.1 KB · Views: 30
Hi ,

What ever has been done as far as naming the shapes is concerned , needs to be redone !

The shape name is available in the Name bar , not the Formula bar ; I have changed the names of the first four shapes ; the remaining need to be done.

I have assigned 3 colours to the 3 status descriptions of Approved , Submitted and Commented. If there are going to be more such status descriptions , please list them along with their colours.

Narayan


Hi Narayan,

You are my saviour!!! This is what I wanted. Thanks for making my work easier :)

Please let me ask you with 3 more things,
1.) How to put "S001" in the name bar as it jumps to cell "S1" whenever I type it in.
2.) Sometimes some numbers I need to use "S001a", "S112a and not "S001", "S112", it doesn't update if I add "a" in "S001" or "S112" for example.
3.) If the status in cell B is blank then the shape should have no fill.

Thanks and Regards,
Michelle
 
Hi Michelle ,

The easy way to change shape names is to first bring up the Object Selection Pane. Home , Find & Select , Selection Pane will do this.

Click on any shape name in the Selection Pane ; click a second time to enter EDIT mode , and change the name. When you move on to the next object , you will see that the earlier selected shape has had its name changed.

Regarding the other changes , I will upload the revised file later.

Narayan
 
Hi ,

See this file ; if you have already named all the shapes in your file , just copy the code from this file into yours.

Narayan
 

Attachments

  • DWall Progress Report.xlsm
    55.2 KB · Views: 31
Back
Top