Colour shapes based on cell values in a lookup

Hi

I am having trouble getting a map I created out of shapes to colour each shape in based on table values, I want for instance a shape I have named NR to lookup NR in a table and if the value in column 3 is between £0 and £10,000 to go red, £10,000 and £50,000 yellow, over £50,000 green.

I have found a code on another thread which did part of the work but I cant seem to manipulate it to work my spreadsheet, I am very new to VBA.

The code I have is;

Please Login or Register  to view this content.

as you can see B3 is a dropdown ("Premium" and "Commission") and when changed id like the colours to update, but if possible also move the lookup to;

Please Login or Register  to view this content.

Any help is very appreciated.

Thank you
Dustin

Hi there,

It's difficult to know how to solve your problem without seeing your workbook, but a good starting point would be to remove the line:

Please Login or Register  to view this content.

and then you'll know at least at which point your code is encountering the error.

Regards,

Greg M

Hi

Thank you for the response and sorry for my late reply, I had left work, I have made some amendments to the code and managed to get it to colour each shape based on 2 number parameters, but I do need 3 at least, I cant figure out how to code for a value between 5 and 10 as an example, only greater than or less than separate.

I have attached a test version of my file where I have deleted most data and used dummy values for the data left. The code is on the "Map" sheet.

I also still cant figure how to change the lookup parameters based on the dropdown in cell B3 so if it says commission the lookup goes to the 4th column instead of the 3rd and the value parameters change to greather than 50k, less than 10k and 10k - 50k.

Also the line you mention to remove only skips over an error where the code tried to select the dropdown as a shape by the looks of it, so I kept it in to skip that.

Thank you
Dustin

Hi again,

Take a look at the attached workbook and see if it's getting closer to meeting your needs. The column used in the lookup table is now appropriate to whichever of Commission or Premium is selected. The map also uses three colours to designate the different amount ranges.

You'll also see that all of the parameters which relate to the layout of the worksheet have been defined as constant values at the start of the routine - this makes it easier to alter your code in response to any changes in the worksheet layout.

The "Map" worksheet uses the following code:

Please Login or Register  to view this content.

I've put in a more specific test for ignoring the DropDown shape. Using "On Error Resume Next" without an accompanying "On Error GoTo 0" is a very dangerous approach, as your code will then ignore ANY errors and can really make a mess of your workbook - especially if objects like ActiveWorkbook, ActiveSheet and ActiveCell are involved!

Hope this helps - please let me know how you get on.

Regards,

Greg M