Need to colour in shapes according to a value

[hawkinsr86]

Hi guys,

I have the a bunch of shapes I need fill with the colour green but I want the shade of green to be darker if the number is higher. Below is my data set. So for example I want Russia to be the greenest (because it is largest) and Luxembourg to be the least green etc...

[TABLE="width: 435"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Autoshape Name[/TD]
[TD]Country[/TD]
[TD]Population[/TD]
[/TR]
[TR]
[TD]S_ALB[/TD]
[TD]Albania[/TD]
[TD]3,639,453[/TD]
[/TR]
[TR]
[TD]S_AUT[/TD]
[TD]Austria[/TD]
[TD]8,210,281[/TD]
[/TR]
[TR]
[TD]S_BEL[/TD]
[TD]Belgium[/TD]
[TD]10,414,336[/TD]
[/TR]
[TR]
[TD]S_BGR[/TD]
[TD]Bulgaria[/TD]
[TD]7,204,687[/TD]
[/TR]
[TR]
[TD]S_BIH[/TD]
[TD]Bosnia and Herzegovina[/TD]
[TD]4,613,414[/TD]
[/TR]
[TR]
[TD]S_BLR[/TD]
[TD]Belarus[/TD]
[TD]9,648,533[/TD]
[/TR]
[TR]
[TD]S_CHE[/TD]
[TD]Switzerland[/TD]
[TD]7,604,467[/TD]
[/TR]
[TR]
[TD]S_CZE[/TD]
[TD]Czech Republic[/TD]
[TD]10,211,904[/TD]
[/TR]
[TR]
[TD]S_DEU[/TD]
[TD]Germany[/TD]
[TD]82,329,758[/TD]
[/TR]
[TR]
[TD]S_DNK[/TD]
[TD]Denmark[/TD]
[TD]5,500,510[/TD]
[/TR]
[TR]
[TD]S_ESP[/TD]
[TD]Spain[/TD]
[TD]40,525,002[/TD]
[/TR]
[TR]
[TD]S_EST[/TD]
[TD]Estonia[/TD]
[TD]1,299,371[/TD]
[/TR]
[TR]
[TD]S_FIN[/TD]
[TD]Finland[/TD]
[TD]5,250,275[/TD]
[/TR]
[TR]
[TD]S_FRA[/TD]
[TD]France[/TD]
[TD]64,057,792[/TD]
[/TR]
[TR]
[TD]S_GBR[/TD]
[TD]United Kingdom[/TD]
[TD]61,113,205[/TD]
[/TR]
[TR]
[TD]S_GRC[/TD]
[TD]Greece[/TD]
[TD]10,737,428[/TD]
[/TR]
[TR]
[TD]S_HRV[/TD]
[TD]Croatia[/TD]
[TD]4,489,409[/TD]
[/TR]
[TR]
[TD]S_HUN[/TD]
[TD]Hungary[/TD]
[TD]9,905,596[/TD]
[/TR]
[TR]
[TD]S_IRL[/TD]
[TD]Ireland[/TD]
[TD]4,203,200[/TD]
[/TR]
[TR]
[TD]S_ITA[/TD]
[TD]Italy[/TD]
[TD]58,126,212[/TD]
[/TR]
[TR]
[TD]S_LTU[/TD]
[TD]Lithuania[/TD]
[TD]3,555,179[/TD]
[/TR]
[TR]
[TD]S_LUX[/TD]
[TD]Luxembourg[/TD]
[TD]491,775[/TD]
[/TR]
[TR]
[TD]S_LVA[/TD]
[TD]Latvia[/TD]
[TD]2,231,503[/TD]
[/TR]
[TR]
[TD]S_MDA[/TD]
[TD]Moldova[/TD]
[TD]4,320,748[/TD]
[/TR]
[TR]
[TD]S_MKD[/TD]
[TD]Macedonia[/TD]
[TD]2,066,718[/TD]
[/TR]
[TR]
[TD]S_NLD[/TD]
[TD]Netherlands[/TD]
[TD]16,715,999[/TD]
[/TR]
[TR]
[TD]S_NOR[/TD]
[TD]Norway[/TD]
[TD]4,660,539[/TD]
[/TR]
[TR]
[TD]S_POL[/TD]
[TD]Poland[/TD]
[TD]38,482,919[/TD]
[/TR]
[TR]
[TD]S_PRT[/TD]
[TD]Portugal[/TD]
[TD]10,707,924[/TD]
[/TR]
[TR]
[TD]S_ROM[/TD]
[TD]Romania[/TD]
[TD]22,215,421[/TD]
[/TR]
[TR]
[TD]S_RUS[/TD]
[TD]Russia[/TD]
[TD]140,041,247[/TD]
[/TR]
[TR]
[TD]S_SR[/TD]
[TD]Serbia[/TD]
[TD]7,379,339[/TD]
[/TR]
[TR]
[TD]S_SVK[/TD]
[TD]Slovakia[/TD]
[TD]5,463,046[/TD]
[/TR]
[TR]
[TD]S_SVN[/TD]
[TD]Slovenia[/TD]
[TD]2,005,692[/TD]
[/TR]
[TR]
[TD]S_SWE[/TD]
[TD]Sweden[/TD]
[TD]9,059,651[/TD]
[/TR]
[TR]
[TD]S_UKR[/TD]
[TD]Ukraine[/TD]
[TD]45,700,395[/TD]
[/TR]
</tbody>[/TABLE]

Could someone please help me with the code as I dont know where to start.

Thanks
Ryan

 

[Damon Ostrander]

Hi Ryan,

Here's a simple user-defined function (UDF) you can use to do this:

Function ColorByArea(ShapeName As String, Pop As Double) As Boolean
   'Fill a shape named ShapeName with color green according to the population.
   'Largest population (Russia) -> dark green   (140,041,247)
   'Smallest population (Luxembourg) -> very light green  (491,775)
   Dim Pfac    As Single   'area factor
   Dim RB      As Integer  'red & blue color values
   Dim G       As Integer  'green color value
   On Error GoTo NotFound
   Pfac = Sqr(Pop / 14041247#)
   RB = (1 - Pfac) * 255
   G = 255 - Pfac * 130
   With ActiveSheet.Shapes(ShapeName)
      .Fill.ForeColor.RGB = RGB(RB, G, RB)
   End With
   ColorByArea = True
   Exit Function
NotFound:
   ColorByArea = False
End Function

I did this as a function rather than a macro so that you could call it directly from your worksheet. You can use it like this. I'll assume your autoshape name, country, and population values are in columns A:C. In column D (in this example D2) of the corresponding rows place the formula

=ColorByArea(A2,C2)

where A2 is the shape name and C2 is the population value. This should color the shape green with lightness according to the corresponding population value.

Of course, this function can be called from a Sub procedure within VBA, but I assumed that calling it from a cell would be more convenient.

To install this function in your workbook go to the Visual Basic Editor (keyboard Alt-TMV, insert a new macro module (Alt-IM), and paste my code into the Code pane. It will be instantly available to use.

 

[Damon Ostrander]

Hi again Ryan,

I forgot to mention that the function returns TRUE if the named shape is found on the active worksheet, otherwise it returns FALSE.

Damon

 

[MARK858]

Cross-posted

Need to change the colour of a shape according to its value

 

[hawkinsr86]

Hi Damon,

I did what you said but all the formulas are returning false for some reason. Any idea why?

Many Thanks

 

[hawkinsr86]

Sorry, I had the shapes on a different sheet. I have now fixed it.

Many Thanks!