Shape Colour based on Value or Cell Fill color

[naveeddil]

Dear All,

I have an excel sheet having some 30+ shapes which is making a map. I have given names to all the shapes as you can see in the below picture.

What i like is that i would like to color the different shapes based on their value in a table.


Excel File: http://s000.tinyupload.com/download.php?file_id=57118960552482861011&t=5711896055248286101138391


Please Help

 

[naveeddil]

I have found a video with some code but unable to make it work https://www.youtube.com/watch?v=sgtHyk1cKk0

 

[My Aswer Is This]

I looked at your question yesterday and you said:

What i like is that i would like to color the different shapes based on their value in a table.

But did not give the name of the Table or in what column of the Table:

So is something like this "12" in column 3 of your Table named Table1 or are you talking about column "C" of a sheet name "Master"

We always need specific details like this.
​

 

[Yongle]

I watched the video (but have not looked at your workbook) and
- created 2 freeform shapes (as in the video)
- renamed them "Khyber" and "Bajour" (2 of the names you have given to your shapes)
- created a table of RGB values (see picture below)
- added the code below

The code loops all shapes in the active sheet and (if found in the table) colour-fills based on lookup RDG values
On Error Resume Next means that any shapes not in your table are ignored and the code continues to run

Sub ColourShapes()
    Dim shp As Shape, R As Long, B As Long, G As Long, aMatch As Range, Rng As Range
    Set Rng = Sheets("[COLOR=#ff0000][I]LookUpSheet[/I][/COLOR]").Range("A:A")
    For Each shp In ActiveSheet.Shapes
            On Error Resume Next
            Set aMatch = Rng.Find(shp.Name)
            On Error GoTo 0
            With aMatch
                R = aMatch.Offset(, 1)
                B = aMatch.Offset(, 2)
                G = aMatch.Offset(, 3)
            End With
            shp.Fill.ForeColor.RGB = RGB(R, B, G)
    Next shp
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]Red[/td][td]Blue[/td][td]Green[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Khyber[/td][td]

255​

[/td][td]

100​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Bajour[/td][td]

0​

[/td][td]

50​

[/td][td]

255​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]etc[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: LookUpSheet[/td][/tr][/table]

 

[naveeddil]

But did not give the name of the Table or in what column of the Table:
So is something like this "12" in column 3 of your Table named Table1 or are you talking about column "C" of a sheet name "Master"
​

​

Thank You for bringing me to more specific.
In the attached spreadsheet, A table on the left is given with different values.
I would like that the shapes having names should be colored based on the values given in Column C.
The color could be based on the cell color (which is already having conditional formatting) OR based on the criteria given in G10:H13.
Example: If Bajour value in Cell C8 is 96% so the shape its shape should be Green filled. If the value of Bajour is Changed to 80% so the shape should be filled with red color.

 

[Yongle]

The code in post#4 works correctly, but would benefit from some editing

the With.. is pointless

            With aMatch
                R = aMatch.Offset(, 1)
                B = aMatch.Offset(, 2)
                G = aMatch.Offset(, 3)
            End With

unless written like this ....

            With aMatch
                R = .Offset(, 1)
                B = .Offset(, 2)
                G = .Offset(, 3)
            End With

 

[My Aswer Is This]

I never open links on this forum to look at user files. And I do not look at videos to see what a user needs. I always like written out details.

[/LEFT]


Thank You for bringing me to more specific.
In the attached spreadsheet, A table on the left is given with different values.
I would like that the shapes having names should be colored based on the values given in Column C.
The color could be based on the cell color (which is already having conditional formatting) OR based on the criteria given in G10:H13.
Example: If Bajour value in Cell C8 is 96% so the shape its shape should be Green filled. If the value of Bajour is Changed to 80% so the shape should be filled with red color.

 

[naveeddil]

I never open links on this forum to look at user files. And I do not look at videos to see what a user needs. I always like written out details.

If you dont open files and focus on written details so what is your feedback on it

 

[My Aswer Is This]

You said:

in the attached spreadsheet, A table on the left is given with different values.​

I never open other peoples spreadsheets.

If you dont open files and focus on written details so what is your feedback on it

 

[naveeddil]

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: LookUpSheet[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the coding and it is working.
Although it is coloring the shape based on the RGB values. I would like to update the shape fill colors based on some coverage value and only four colors i.e. Red, Orange, Green and Grey. (No more colors)

The Coloring should be based on below ranges:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Range
[/TD]
[TD]Color name[/TD]
[/TR]
[TR]
[TD]<90%[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]90%~95%[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]95%~105%[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]>105%[/TD]
[TD]Grey[/TD]
[/TR]
</tbody>[/TABLE]