VBA CODE TO CHANGE SHAPE COLOUR BASED ON CELL VALUE

kumshan450

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 2016
I am currently working in a project where i have change the district color based on its cell value.

For eg in Sikkim state, there are 4 districts.

NORTH SIKKIM DISTIRCT MAP SHOULD CHANGE TO NORMAL COLOUR (GREY) AS ITS VALUE IS LESS THAN 1000
EAST SIKKING DISTRICT MAP SHOULD CHANGE TO BLUE COLOUR AS ITS VALUE IS GREATER THAN 1000
ANS SO FOR REST OF THE TWO DISTRICTS.

ANYTHING GREATER THAN OR EQUAL TO 1000 DISTRICT MAP SHAPE SHOULD CHANGE TO BLUE COLOUR OTHERWISE GREY.
IDValueDISTRICT
M1
300​
NORTH SIKKIM
M2
200​
WEST SIKKIM
M3
1000​
SOUTH SIKKIM
M4
50000​
EAST SIKKIM

THERE ARE 4 SHAPES IN ASSAM DISTRICTS. ALL SHAPES ARE NAMED BY M1, M2, M3 and M4

VBA CODE

Private Sub CommandButton1_Click()
For i = 1 To 5
API = Sheet1.Cells(i + 1, 2)
Select Case API
Case Is < 1000
Sheet1.Shapes.Range(Array("M" & i)).Fill.ForeColor.RGB = RGB(255, 0, 0)
Case Else
Sheet1.Shapes.Range(Array("M" & i)).Fill.ForeColor.RGB = RGB(255, 50, 0)
End Select
Next i
End Sub

I am trying to import above code but i am getting error - Run time error 1004 The item with the specified name wasn't found.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
491
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Change shapes name from M1 to M5 into x_M1 to x_M5 and then with:
Excel Formula:
With Sheet1
    For i = 1 To 5
        API = .Cells(i + 1, 2)
        Select Case API
            Case Is < 1000
                .Shapes("x_" & .Cells(i + 1, 1).Value).Fill.ForeColor.RGB = RGB(255, 0, 0)
            Case Else
                .Shapes("x_" & .Cells(i + 1, 1).Value).Fill.ForeColor.RGB = RGB(255, 255, 0)
        End Select
    Next i
End With
will work.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,852
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
ALL SHAPES ARE NAMED BY M1, M2, M3 and M4
How? You cannot name a shape with something like M1 as it is a valid range reference.
 

kumshan450

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 2016
Change shapes name from M1 to M5 into x_M1 to x_M5 and then with:
Excel Formula:
With Sheet1
    For i = 1 To 5
        API = .Cells(i + 1, 2)
        Select Case API
            Case Is < 1000
                .Shapes("x_" & .Cells(i + 1, 1).Value).Fill.ForeColor.RGB = RGB(255, 0, 0)
            Case Else
                .Shapes("x_" & .Cells(i + 1, 1).Value).Fill.ForeColor.RGB = RGB(255, 255, 0)
        End Select
    Next i
End With
will work.
Thank you very much its working fine. Problem solved.
 

kumshan450

New Member
Joined
Apr 22, 2022
Messages
3
Office Version
  1. 2016
Hi & welcome to MrExcel.

How? You cannot name a shape with something like M1 as it is a valid range reference.
Yes we can give name to an shape object. Please find the file attached
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,852
Office Version
  1. 365
Platform
  1. Windows
You can give them names, but those names cannot be valid range references like M1.
You cannot attach files here, as it is not allowed.
 

Forum statistics

Threads
1,176,512
Messages
5,903,474
Members
435,031
Latest member
traceson

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
Top