I'm working on a macro that automatically highlights a map based on the data within my worksheet. There are 35 areas on my map each with their own shape that is named for the number of the map area (100, 110, 120 to 750, 760, 770).
I need to be able to select the shape corresponding to the area so I can properly format it...
Here's my code thus far:
BuyersMarket = ActiveSheet.Range("U2")
SellersMarket = ActiveSheet.Range("U3")
For Row = 3 To 37
Area = Cells(Row, 2).Value
Inventory = Cells(Row, 3).Value
If Inventory > BuyersMarket Then
ActiveSheet.Shapes.Range(Array(Area)).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 128, 0)
.Transparency = 0.5
.Solid
End With
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 128, 0)
.Transparency = 0
End With
ElseIf Inventory < SellersMarket Then
ActiveSheet.Shapes.Range(Array("Area760")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0.5
.Solid
End With
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
Else
ActiveSheet.Shapes.Range(Array("Area760")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0.5
.Solid
End With
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
End With
End If
Next
However, this part doesn't work:
"ActiveSheet.Shapes.Range(Array(Area)).Select"
So I need to know how to properly specify a variable that I can use to select the shape so that I can assign it the right color.
Any ideas?
Bookmarks