fill color of shape based on value

[africadan]

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!!!

 

[Sohil]

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

 

[NARAYANK991]

Hi Sohil ,

See your file now.

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

Narayan

 

[Sohil]

Hi Narayan

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

Regards
Sohil

 

[NARAYANK991]

Hi Sohil ,

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

Narayan

 

[Sohil]

Sorry ,but there will be no more changes

 

[NARAYANK991]

Hi Sohil ,

See the file now.

Narayan

 

[Sohil]

Thanks a lot,It works Perfect

Regards

Sohil

 

[Farhan]

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!

 

[Farhan]

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.

 

[Sandeep H C]

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??

 

[Sandeep H C]

Hello All could you please Look into above Problem and help me in doing it.


Thanks

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Sandeep H C]

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.

 

[Sandeep H C]

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.

 

[michelleponan]

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.

 

[NARAYANK991]

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

 

[michelleponan]

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

 

[michelleponan]

Sorry I forgot the attachment.

 

[NARAYANK991]

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

 

[michelleponan]

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

 

[NARAYANK991]

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

 

[michelleponan]

Thanks Narayan I tried naming it and it worked

 

[NARAYANK991]

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

 

[michelleponan]

Hi Narayan,

Sorry for the late reply. Thanks for the help. it's what I need now I can easily track my progress report.