Change Shape Color based on Cell Value (Excel VBA)

[Valeriew]

I am new to VBA and I want to make an interactive map consisting of 10 districts, where each district would be colored, for example, green if person A is assigned to that district or red if person B is assigned to that district . I used this code from other threads and there is no error in the formula, but it seems like the shape color doesn't change. Can anyone tell me what is wrong in the code?

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 To 10

If Sheet31.Cells(i, 2) = "Person A" Then
Sheet31.Shapes.Range(Array("Praha_" & i)).Fill.ForeColor.RGB = RGB(237, 125, 49)
ElseIf Sheet31.Cells(i, 2) = "Person B" Then
Sheet31.Shapes.Range(Array("Praha_" & i)).Fill.ForeColor.RGB = RGB(255, 192, 0)
ElseIf Sheet31.Cells(i, 2) = "Person C" Then
Sheet31.Shapes.Range(Array("Praha_" & i)).Fill.ForeColor.RGB = RGB(91, 155, 23)
ElseIf Sheet31.Cells(i, 2) = "Person D" Then
Sheet31.Shapes.Range(Array("Praha_" & i)).Fill.ForeColor.RGB = RGB(165, 165, 165)
ElseIf Sheet31.Cells(i, 2) = "Person E" Then
Sheet31.Shapes.Range(Array("Praha_" & i)).Fill.ForeColor.RGB = RGB(96, 165, 165)

End If
Next i

End Sub

 

[Marc L]

Where this event procedure is located ?​

 

[Valeriew]

​

Where this event procedure is located ?​

All events (shapes 1-10) are located in Sheet 31

 

[Marc L]

As a Shape is not a VBA event procedure so same question again : where is located this code, in which module ?!​

 

[RoryA]

By the way, you can just use Sheet31.Shapes("Praha_" & i) rather than Sheet31.Shapes.Range(Array("Praha_" & i))

 

[Valeriew]

As a Shape is not a VBA event procedure so same question again : where is located this code, in which module ?!​

Sorry for not understanding your previous question correctly. This code is located in Worksheet 31 and not in any module (please refer to picture attached), I see similar codes are also applied in specific worksheet instead of module?

 

[Marc L]

​

As it's a worsheet event procedure so it must be located in the appropriate worksheet module which seems the case here.​

​

Let's check your need : when a name is updated in a cell within the range B1:B10 the color of the relative shape according to the row index must change​

if the entry is between Person A & Person E, am I right ?​

​

What is the default color when nobody is assigned to a district ?​

​

 

[Marc L]

​

As a reminder your original code is case sensitive …​

 

[Valeriew]

​

As it's a worsheet event procedure so it must be located in the appropriate worksheet module which seems the case here.​

​

Let's check your need : when a name is updated in a cell within the range B1:B10 the color of the relative shape according to the row index must change​

if the entry is between Person A & Person E, am I right ?​

​

What is the default color when nobody is assigned to a district ?​

​

I uploaded dummy data on google drive for you to visualize it easier.

Yes, you are correct. The input is between Person A to Person E (in a drop-down list); for example if A1 (Praha_1) is assigned to Person B in B2, then the shape with the name Praha_1 should change color to RGB(255, 192, 0). If I change it again to Person A, then the color of Praha_1 shape should change to RGB(237, 125, 49).

The default color when nobody is assigned to a district should be RGB(255,255,255).

 

[Marc L]

According to your attachment, evaluate this idea : instead of hardcoding colors and names,​

as you are using a drop-down list in Sheet2 so using the Sheet2 column B for the color should be better like "237,125,49" ?​