Hey all I'm working on something where a map of the UK, which is divided in to 15 regions, is coloured in based on how that region ranks in some data. I have formulae that calculates the RGB colour that it should be coloured in as, but I constantly get "Subscript out of range" error at the 'Forecolor' line on the code below when trying to run the code.
I have some VBA script that looks at the RGB value that's been calculated in a field and then fills the selected shape with colour based on that RGB code. I've done this on another worksheet a couple of years back and it worked fantastically well so I know it's doable, but I can't figure out where I might be going wrong and I've lost that other workbook!!
Here's the code. The array "Reg 01" is essentially Scotland on a map of the UK, RGB code/value is a formula in the "Data for report" tab and I've pasted a screenshot of what that data looks like just below the code.
I have some VBA script that looks at the RGB value that's been calculated in a field and then fills the selected shape with colour based on that RGB code. I've done this on another worksheet a couple of years back and it worked fantastically well so I know it's doable, but I can't figure out where I might be going wrong and I've lost that other workbook!!
Here's the code. The array "Reg 01" is essentially Scotland on a map of the UK, RGB code/value is a formula in the "Data for report" tab and I've pasted a screenshot of what that data looks like just below the code.
VBA Code:
ActiveSheet.Shapes.Range(Array("REG 01")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(Sheets("Data for report").Range("F62").Value, Sheets("Regional And Divisional").Range("G62").Value, Sheets("Regional And Divisional").Range("H62").Value)
.Transparency = 0
.Solid
End With