Shape Hyperlink Screentip macro

Hello,

I am currently trying to make a macro that will update a shapes hyperlink screentip. I am using a range that has my data and shape names. Please let me know what is the matter with the current macro I have been trying to develop.

I have attached my excel file, which contains only the bare essential information that you would need to understand what my goal is. the hyperlinks have already been created to go to sheets that I have deleted because of sensitive data.


Sub RPMStates()
'
'
'
Dim cstate As Integer
Dim StateName As String
Dim StateValue As Integer
Dim rngstates As Range

Set rngstates = Range(ThisWorkbook.Names("rpm").RefersTo)

With Worksheets("map")
For cstate = 1 To rngstates.Rows.Count
StateName = rngstates.Cells(intState, 1).Text
StateValue = rngstates.Cells(intState, 5).Value

Shape.Range(Array(StateName)).Select
Selection.ScreenTip = StateValue

Next
End With

End Sub


Thank you for your time,
Andy