• 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,

i also need a vba code to color different shapes based on E column where in the E formatted with conditional if.. can anyone help me.
 

Attachments

  • Book1t.xlsx
    17.6 KB · Views: 23
Hi ,

Please take the time and effort to describe your requirement in as much detail as possible ; if you treat problem description as a burden , it is very unlikely that others will take the trouble to understand your requirement and provide a solution.

1. You have 5 shapes in your worksheet ; which shape should have what color ?

2. How is the color to be decided on the basis of what you have in column E ?

3. What variations will there be in your data and your shapes ? How will you be extending your requirement , or will your requirement be just these 5 shapes ?

Narayan
 
its a sample only having 30 shapes with different names...
1. shape color depned on cell color i.e. 1st shape link to E2, like this
2. color of the cell will be conditional formatting and the same will be updated into shapes

is the given details sufficient...
 
Hi, can I please jump on this thread. I am trying to link a shape colour to a cell value, and have tried numerous bits of VBA coding but so far none have worked. Basically if the cell value is <14 I want the shape to turn green, >14 red.
I attach a simple example file, no coding in yet though as I'm stumped!
Many thanks in advance!
 

Attachments

  • Vick Test.xlsm
    9.8 KB · Views: 23
Good evening, I had a look at the file above (Vick Test) and it is exactly what I need too, however I need to have multiple shapes on the the same worksheet, all with different thresholds for a report. How would you add in say another couple of shapes please? I had a go but there were errors with ambiguous names then when I altered those there was an 'invalid use of Me keyword'. Thanks
 
Hi ,

Please upload your workbook with the shapes ( at least 2 of them ) , the thresholds , and the entered values which will be used to color the shapes.

Narayan
 
Hi Guys,

This seems like the forums I've been looking for for so many days.
I made a massive list/db of things that I collect and I created a few tabs that allow me to create labels, compare those elements ... etc.

I also have pictures for all these items, same formats, same sizes of pictures, all stored in the same folder.
I've been scratching my head for days to try to display these images according to a choice a make in a dropdown list.
Apparently, working directly with images reveals to be a massive pain in the butt, but there is a workaround, having a shape for which I can change the "fill" properties. I've discovered that recently, so I finally felt some hope in my quest.

Now the thing is, I am an absolute noob in VBA, I've done some programing before, but I find myself to be a little lost here.

So summary of the problem.
On tab has all the info.
One tab is used to display comparable info on those elements. Nice display, properties comparisons etc. all done with VLOOKPUP. One of the field from my db has the direct/absolute path of the picture of the element.

On the compare tab, I can compare up to 4 items at a time. So I have 4 shapes for which I'd like to change the fill according to the element chosen from the dropdown list.

How can I, using VBA or else, detect a change in a dropdown, recuperate the info, path, etc, according to the choice and fill the respective shape with the image?

Thanks a lot for reading me and for the help.

Carver
 
Hi Narayan,

thanks for your answer and support.
Instead of asking you to do it for me, I'd hope to learn to do it on my own based on recommendations.

Basically, on sheet 2, with a VLOOKUP, i retrieve the data from sheet1, based on a selection from a drop down list (populated by the main elements on sheet 1).

On sheet 2, since it's a comparator tool, I have 4 times the same display, 4 dropdowns, looking up to the same list but different elements.

Every items on the list on sheet 1 has one cell with a path, path to an image of the element.

The 4 displays all retrieve the info, display the info, etc. And they all have a place fo an image, in a shape.
What I wish to do is, when I select an element on the dropdown, it automatically use the corresponding image as the "fill" for the shape.
All four shapes are "Rounded Rectangle 1" (2, 3, and 4).

Could you help me with a code sample that I could rework on and learn to understand than myself apply.

change in the list --> lookup path, copy it in a cell --> apply image as fill argument for shape.

Thanks a lot for your time.
 
Last but not least, I am working on Excel 2011 ... for Mac, as so I have seen, file path as they are can create loads of troubles.

here is a short example of code that I have been working on ... and it fails. I get runtime error.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shp1 As Shape, shp2 As Shape, shp3 As Shape, shp4 As Shape
Dim path1 As String

'Which cell is changed?
Select Case Target.Address
Case Range("C7").Address

If IsEmpty(Target) Then Exit Sub

Application.EnableEvents = False
Set shp1 = ActiveSheet.Shapes("Rounded Rectangle 1")
shp1.Fill.UserPicture ("/Users/BLAH/labels/image.png")
Application.EnableEvents = True


End Select
End Sub
Click to expand...
 
Hi ,

It would help if you could upload your workbook with the shapes and code in it ; even if I have to list out the steps that you should carry out one after another , I would like to code all of it , test it out , and then detail the steps.

Narayan
 
Hi Narayan,

Thanks so much for the offer to help.
While I asked for help on the forum, as you can imagine, I myself continued working to do what I wished to achieved and I managed to do so this morning.

Now I do have another question.
So I managed to fill shape dynamically with images, it's working great, but it's taking space on the file size since I have pictures on shapes at all times.
I've tried to find a way to clear the shapes but so fa the only thing I found to do the trick was to render the filling invisible, but that doesn't really remove the picture.
Would you happen to know which method or property I can use to clear/remove/delete the filling, without removing the shape ?

Thanks a lot.

Carver.
 
NARAYANK991 said:
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
Click to expand...


Hi Narayan,

May I know how to add in visual basics if I will need to refer to 2 worksheets,
"With Worksheets ("D-Wall", "Barrette")"?
Is this correct?

Thanks so much.

Regards,
Michelle
 
michelleponan said:
Hi Narayan,

May I know how to add in visual basics if I will need to refer to 2 worksheets,
"With Worksheets ("D-Wall", "Barrette")"?
Is this correct?

Thanks so much.

Regards,
Michelle
Click to expand...
Hi ,

I am not sure about the context , but take a look at this code , and see if you can adapt it to suit.
Code: 
Public Sub temp()
           Dim ArrayofSheets As Variant
           ArrayofSheets = Array("Sheet2", "Sheet3")
      
           For i = 0 To UBound(ArrayofSheets, 1)
               With Worksheets(ArrayofSheets(i))
                    ' Your code
                     '
                     '
                End With
           Next
End Sub
Narayan
 
NARAYANK991 said:
Hi ,

I am not sure about the context , but take a look at this code , and see if you can adapt it to suit.
Code: 
Public Sub temp()
           Dim ArrayofSheets As Variant
           ArrayofSheets = Array("Sheet2", "Sheet3")
     
           For i = 0 To UBound(ArrayofSheets, 1)
               With Worksheets(ArrayofSheets(i))
                    ' Your code
                     '
                     '
                End With
           Next
End Sub
Narayan
Click to expand...


Dear Narayan,

I tried but the "D-Wall" sheet doesn't update the color.
Please see attachment.
Thanks.

Regards,
Michelle
 

Attachments

  • T219 DWall Progress Report Cripple Siding.xlsm
    371.8 KB · Views: 8
NARAYANK991 said:
Hi ,

See if this is OK.

Narayan
Click to expand...
This is amazing work. Thank you so much for sharing. So I am using this on a dashboard. when I copy and paste my options of color to another cell, it does not change the relevant shape. it needs me to double click on the cell or filter for the color I want. how can I refresh the code so that even when I paste over a different option it automatically changes the relevant shape. see attached
 

Attachments

  • VBA_code for Color coding shapes.xlsm
    42.2 KB · Views: 10
You must log in or register to reply here.
Back
Top