Set a shape colour based on variable RGB value

jonboy_t

New Member
Joined
Nov 15, 2018
Messages
2
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.

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

1674221286462.png
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Ignore me! I spotted what I'd done wrong literally as I pressed 'Post' - I changed the name of a tab after writing the code, but only changed the tab name on one of the values. So it could find the R of RGB, but the G and B were still on the old tab.

What a moron I am!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top