Thematic Map in Excel - VBA question

[greenskies]

There is a website that I got instructions on how to create a thematic map that allows the shapes in map I created change color based on certain range of values. I created 3 shapes on map. However as I run macro it gives me error below.

Error message:

Run Time error '1004'
Method 'Range' of object' _Global' failed

Macro is below -
Sub marcoColor()

For i = 4 To 6
Range("actReg").Value = Range("Sheet1!A" & i).Value
ActiveSheet.Shapes(Range("actReg").Value).Select
Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color

Next I



End Sub

Item in bold is where excel states the error is.

Any suggestions as to reason why this macro is not working for me?

 

[StephenCrump]

Welcome to the Forum!

1. Do you have a range called actRegCode, e.g. as opposed to actReg whose values you are setting in the first line of the loop?

2. What is the value of actRegCode when the code errors? it should be a valid range name for your code to work.

 

[greenskies]

Thanks for replying back.


1. I've labeled a cell "actRegCode".

I've labeled several cells. See below:

actReg = cell contains info about name of shape
actRegValue = cell contains a vlookup and brings up value based on actReg value
actRegCode = cell contains a vlookup and brings up name of another cell that is filled with color I want shape to be based on certain value

Color0-Color8 = there are 9 cells labeled (color#). each cell has a particular color.


2. The value of actRegCode is a cell name. the cell is filled with color that shape should be.

The first excel page is the main page with shapes and a list to select from. The second excel page has all the information. Would the code change in this case?

If you would like, I could email excel page to you.

 

[StephenCrump]

The error message says you have a problem here:

Range (Range("actRegCode").Value)

If you have a cell named actRegCode, then Range("actRegCode").Value will work OK, and will return some value "SomeValue".

So the problem is with Range("SomeValue")

"SomeValue" can be either a valid cell reference, e.g. A1, or another named cell, e.g. Color0.

The error suggests that "SomeValue" is not a valid reference, i.e. your actRegCode cell contains an invalid reference.

If you would like, I could email excel page to you.

We'll need to keep it in-Forum (see Forum rules). If you're happy to post your workbook for anyone to see, you can upload to a drop box site and post the link here.