Conditional format a free form shape based on a value

[Kevlar]

Hi,

This is something I have been epically failing at for a long time now.
What I want to do is simple at face value.
My inability to pick up VBA at a reasonable pace has not helped. Text book learning is not for me! Examples do the job.

Basically...

I have a range of colours, Green > Amber > Red.
This defined in a key against a value in a cell.

I have post codes for a county in the east of England. Each of these post codes is assigned a value which corresponds to the key. I wish to have an end product which is a dynamic s/s that can handle changing values in each of the post codes, and update the free form shapes as such.

My question is, how can i link the free form shapes (that represent a post code) to the post code and format this based on its value?

I have browsed the web for hours, watched hours of videos, bought a VBA for dummy's book. None of which have helped. This is my first post of this nature on any site.

If someone could take a look at my s/s and provide some input that would be really appreciated.

Thank you.
Kevlarence

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Hui]

My only comment would be that I would rename the shapes according to the Postal codes
So instead of having my Freeform shapes names like Freeform27
I would rename it to PE19

Then adjust the code accordingly

 

[NARAYANK991]

Hi ,

I would do away with all freeform shapes , and resort to what are called choropleth maps , which are available on the Internet.

To see how these can be used in Excel , check out all the blog posts here :

http://www.clearlyandsimply.com/clearly_and_simply/choropleth-maps/

Narayan

 

[Kevlar]

Narayan,

Thank you for the code. It looks and works great.
I have expanded the range reference to a further row and referenced an additional shape and removed all borders following the same tone of code (shp.Line.Visible = msoFalse)
I'm still trying to reverse engineer what the code is doing.. and how, but i've learnt more from doing that than reading 'VBA for dummies'. I hope to figure it out as i become more familiar with it over the coming weeks.
The choropleth maps look like they are a bit beyond me unfortuantely, although i did give the link that you posted a robust read through. Perhaps something for the future. I'll try and work out VBA a bit more for the time being and finish this model.
Please check out my reply to Hui !

Hui,

Thanks for your input. I gave your line of comment some thought before i uploaded the file, following some unsuccessful trouble shooting online. I can break the barriers that have prevented me achieving this into two:

1. when i try to edit the name of a shape in excel and press enter (in the name reference bar), it does not change. Any thoughts on why?
2. (given my poor understanding of VBA), how exactly would this change now impact the code that Narayan has very kindly posted on the thread?

Good idea though, and something i think i will need to overcome to grow the model to cover additional regions (as free form shapes seem to repeat themselves i.e. 2x 'freeform2'.

Thank you very much guys. You've been a great help.

K

 

[NARAYANK991]

Hi ,

On the Home tab in the Ribbon , click on Find & Select ; click on Selection Pane.

Select the object and change its name there.

Narayan

 

[NARAYANK991]

Hi ,

See the attached file , where I have changed the names of 3 shapes , and revised the code accordingly.

If you change the names of all the remaining shapes , everything should be OK.

Narayan

 

[Kevlar]

Hi Narayan,

Thanks so much for all your help so far. Its been invaluable.

I have attached the latest file. I was hoping you would please share with me one more piece of guidance/ information.

I wish to expand the model beyond the PE area code, to encompass multiple districts. The code (below) references 'PE' only. I wasn't to sure what the "*" was used for. More generally, I tried a few approaches to no avail.

If shp.Name Like "PE" & "*" Then
ShapeNum = Val(Application.WorksheetFunction.Substitute(shp.Name, "PE", ""))

Would you please kindly recommend how to go about including additional district codes, i.e. CB, IP, CO etc. ?

Thank you.
K

 

[NARAYANK991]

Hi ,

See the attached file ; this takes care of 4 postcode prefixes ; as and when you add more , change the following line of code to include the added ones.

PostCodes = Array("PE", "CB", "IP", "CO")

Also , the present code has fixed the last row of data at 300 ; if your data extends beyond this , change the following line of code to include the added data.

ShapeVal = Application.Evaluate("=VLOOKUP(" & """" & shpname & """" & " , $H$2:$J$300 , 3 , FALSE)")

Narayan

 

[Kevlar]

Hi all,

Its been a while since i looked at this, picking it up again.

Is anyone please able to help trouble shoot the attached code?

It keeps error-ing on me.shapes

Thanks,
K