On a similar project (with 73 shapes), I decided to name them with a unique name/number combination, i.e. Mark1, Mark2, etc. You could use the cell reference plus some other name to identify (i.e. Markb1) - you can't use B1 because that is reserved for the cell itself.
Here is the code I use for them, with arrows indicating the direction and color (up/green, flat/orange, down/red)
Code:
Sub OriginalValueCheck()
'This checks the cells in Column A on worksheet Work (rows 10 through 77)
Application.ScreenUpdating = False
'This first command unhides rows 9, 16, 24
UnhideRows
Dim i As Long
Sheets("Work").Select
For i = 2 To 73
Select Case Cells(i, 1).Value
Case Is < 0: RedAutoShape i
Case Is > 0: GreenAutoShape i
Case 0: OrangeAutoShape i
End Select
Next i
'This calls the macro for the reverse direction/color of rows 23 and 24
OriginalValueCheckS
Sheets("Highlights").Select
'This macro re-hides rows 9, 16, 24
HideRows
Application.ScreenUpdating = True
End Sub
Sub RedAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeDownArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 10
.Line.ForeColor.SchemeColor = 10
.Rotation = 0#
End With
Sheets("Work").Select
End Sub
Sub GreenAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeUpArrow
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 57
.Line.ForeColor.SchemeColor = 57
.Rotation = 0#
End With
Sheets("Work").Select
End Sub
Sub OrangeAutoShape(i)
Sheets("Highlights").Select
With ActiveSheet.Shapes("MattAS " & i)
.AutoShapeType = msoShapeLeftRightArrow
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 52
.Line.ForeColor.SchemeColor = 52
.Rotation = 0#
End With
Sheets("Work").Select
End Sub
dBforums
>
shapes in certain range
03-03-04, 08:44
Linear Mode
