Keep running into Run-time error '5' with VBA Macro-CodePudding

A few years back I built a interactive heatmap in Excel with Freeforms forming a complete map of the country with all regions.

Every region has a specific KPI percentage, which on it's turn colours the freeform shapes via a VBA macro.

However, I now keep running in a Run-time error '5': Invalid procedure call or argument and I cannot find the bug in the code.

Does anyone see what I don't?

Sub HEATMAP()

For i = 1 To 139

a = Sheet3.Cells((1   i), 6)
b = Sheet3.Cells((1   i), 5) * 28 - 28
c = Sheet3.Cells((1   i), 5) * 28 - 9
d = 89 - Sheet3.Cells((1   i), 5) * 9 - 9

Sheet2.Shapes.Range(Array("Freeform " & a)).Line.ForeColor.RGB = RGB(80, 80, 80)
Sheet2.Shapes.Range(Array("Freeform " & a)).Fill.ForeColor.RGB = RGB(b, c, d) "<- this line produces the error"

Next i

    ActiveWorkbook.RefreshAll
    
End Sub

Sheet 3 contains a list of all the regions with their freeform-name of each region's object and their KPI percentage, brought back to 10 categories (one for each 10% bracket), which should return a specific color.

This color should be calculated with the values b, c and d in the VBA. (from lowest bracket: yellow to highest bracket: green)

The sheet contains a header.
The 5th column has the colour bracket (numbered 10 - 1, with 10 as the worst bracket [0-10%] and 1 as the best bracket [90-100%])
The 6th column has the freeform number of the objects, creating the map.
There are a total of 139 regions (and therefore freeforms)

Sheet 2 contains the map with a button which triggers this VBA, so the user can "colour the map" with it, based on the KPI values.

What do I keep missing?

Thanks in advance!

CodePudding user response：

As big ben says, what are b, c and d when the error hits?

Might want to wrap it with an

On Error GoTo ErrorHandler1 
   Sheet2.Shapes.Range(Array("Freeform " & a)).Fill.ForeColor.RGB = RGB(b, c, d) '<- this line produces the error
   dummy = 1  'Just a dummy line so you can resume out of the error and see what inputs produced the invalid input val
Exit Sub 

ErrorHandler1: 
    stophere = 1   (and put a breakpoint on this line)
Resume Next

That's a slightly ugly way of getting into the code on the iteration its falling apart and letting you look around for both the invalid value and where its coming from.