VBA: Change Shape Color based off cell value

Hello,

First post. Just getting into using VBA/Macros for Excel. I have ran into a problem. The following macro worked fine with one excel file, and then when I started to reference data from a pivot table I started to get an error. (Error 1004, Method "Range of object'_Global' failed)

The macro below is to simply change a shape color based off a range value compared to another range value. Can you please take a look and let me know what I am doing wrong.

Sheet I am referencing is titled: PIVOT Tables
Shape I am referencing is titled: Chicago

Thanks!!

Please Login or Register  to view this content.

When your sheet name has a space in it, Excel requires single quotes in any reference to it. So, change all

Range("PIVOT Tables!xxxx")
to
Range("'PIVOT Tables'!xxxx")

The single quotes go just before the P of PIVOT and just before the !