VBA for conditional formatting shapes

[jcuffel]

Greetings,

I've found this forum very helpful so far, but haven't been able to crack this one just yet.

I've got a spreadsheet (see below and attachment) with the 50 U.S. states and their % level of recovery. I would like have each state's color change according to its recovery level and the corresponding legend.

I'm brand new to VBA and excited to learn it. Any help would be greatly appreciated!

Thanks,

Jason

 

[jcuffel]

Before anyone goes too far on this -

I realize there are 50 states. If you could just show me how to do 1 or 2 states, I'm sure I could figure out the rest.

Thanks!

 

[Smallman]

Hi Jason

I was going to ask about Hawaii and Alaska but you seem to have addressed that. Your map does not include those two states.

The key is to set the shapes up so they all have a name that relates to the State. You can then leverage colour on top with some lookups and a little bit of VBA code.

I wrote a tutorial on the technique.

Heat Map Excel
Also there is a slightly more complex heat map example here.

American Heat Map Dashboard

The code to run the procedure is as such:

Option Explicit
 
Sub ColourMeBad()
  Dim shp As Shape
    Dim ar As Variant
    Dim var As Variant
    Dim i As Integer
    Dim j As Integer
 
    var = Sheet2.[H2:J5] 'List Sheet, Adds colours
    ar = Sheet1.Range("a5", Sheet1.Range("a65536").End(xlUp))
 
    For i = 1 To UBound(ar)
        j = Application.VLookup(ar(i, 1), Sheet1.Range("A5:C54"), 3, 0)
        Set shp = Sheet1.Shapes("S_" & ar(i, 1))
        shp.Fill.ForeColor.RGB = RGB(var(j, 1), var(j, 2), var(j, 3))
    Next i
End Sub

Your file is attached I have added some random colours so you can see how it works. I also used my map rather than your one. It is all good though.

Any questions let me know?

Take care

Smallman