Help with Automatic Coloring of Shapes (States) in Excel

[bkanne]

Can someone please help me automate the fill coloring for named shapes in Excel? I've attached a template that should be pretty self-explanatory.

Effectively, I would to be able to run the macro by pressing the Command Button, and then each state, which is a named shape in the map, would be colored according to what is in the RGB columns.

Thank you so much for any help!

Regards,
Ben

 

[SirJB7]

Hi, bkanne!
Give a look at the uploaded file. This is the code behind the button:

Option Explicit

Sub CaranDAche()
    '
    ' constants
    Const ksWS = "Sheet1"
    Const ksStates = "StatesTable"
    '
    ' declarations
    Dim rng As Range
    Dim I As Integer, J As Integer, K As Integer
    '
    ' start
    With Worksheets(ksWS)
        .Activate
        Set rng = .Range(ksStates)
    End With
    '
    ' process
    With ActiveSheet
        For I = 1 To .Shapes.Count
            With .Shapes(I)
                Debug.Print I, .Name,
                For J = 1 To rng.Rows.Count
                    If .Name = rng.Cells(J, 1).Value Then Exit For
                Next J
                If J <= rng.Rows.Count Then
                    .Select
                    .Fill.ForeColor.RGB = RGB(rng.Cells(J, 3).Value, rng.Cells(J, 4).Value, rng.Cells(J, 5).Value)
                End If
            End With
        Next I
        [A1].Select
    End With
    '
    ' end
    Set rng = Nothing
    Beep
    '
End Sub

Regards!

 

[bkanne]

BRILLIANT. Thank you dearly!

 

[SirJB7]

Hi, bkanne!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!