color formatting of shape based on cell value

[Dave G]

Hi all,

I am trying to change the color of a shape based on the contents of particular cell. In Column B I have 9 different potential names (dave,tom, joe, etc) down 63 rows. I also have drawn 63 shapes that form a map. I want to somehow tie each shape to a cell so that the shape colorscheme will change when the user makes a name selection. So, if the user selects "dave" for all 63 rows, that map would be all one color. If the user selects "tom" for all 63 rows, it would be a different color (i.e. 9 different color possibilities). The user will actually select a mix of names making the map a many colored map.

Any advice on how to tie these together? I was hoping conditional formatting would do it, but aparently that only works on cells.

Thanks in advance!!!

-Dave

[abousetta]

Hi,

From your description you need a change_event that will be triger a change of the color. If you could post a dummy workbook then we elaborate further.

abousetta

[Dave G]

I started out with this code, and it works as a simplistic code. But, I have so many rows I'm editing it doesnt make sense to do this for every cell The code starts at $B$3, checks the name, and then edits the appropriate shape. So, if b4 is dave, it will set the shape schemecolor to 2 for shape cl04.0. if b4 is tom, it will set the colorscheme to 3 for shape cl04.1. I tried to continue this all the way down to b62 but when I ran it, it turns out my code was too long and wouldn't run. I need to figure out how to do this as a subroutine i guess.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Select Case Target.Value
Case Is = "dave"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 2
Case Is = "tom"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3
Case Is = "lee"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 4
Case Is = "john"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5
Case Is = "joe"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 6
Case Is = "bal"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 7
Case Is = "csp"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Case Is = "eve"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Case Is = "dou"
ActiveSheet.Shapes("cl04.0").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 21

End Select
End If
If Target.Address = "$B$4" Then
Select Case Target.Value
Case Is = "dave"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 2
Case Is = "tom"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3
Case Is = "lee"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 4
Case Is = "john"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5
Case Is = "joe"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 6
Case Is = "bal"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 7
Case Is = "csp"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Case Is = "eve"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Case Is = "dou"
ActiveSheet.Shapes("cl04.1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 21

End Select
End If

[abousetta]

Hi Dave,

You need to put Code Tags around your code (#). Please refer to the forum rules if you need instructions.

Without testing since I have no workbook to test it on, I could imagine it could be something like this:

Please Login or Register  to view this content.

Hope this helps.

abousetta

[Dave G]

In this example, the names are school abbreviations. The zones are geographic areas. Depending on which school is assigned to which zone, I'd like the map to change colors of that zone so that I can look at the map and see which geographic zones go to which school. I have actually renamed the shapes to match the exact name of the zone as described in column C, thinking I might be able to tie the two together somehow. thoughts?

-Dave

[Dave G]

I tried this:

Please Login or Register  to view this content.

So that when I set a cell to fhr, it gets the zone from the cell next to it (which happens to have a corresponding named shape), and then selects that shape and set it's color scheme.
I'll just repeat that for all 9 names!

[abousetta]

Will this fill your needs?

Please Login or Register  to view this content.

abousetta

[Dave G]

grrr, not getting the expected behavior. Attached an updated version with the code. It works for cell B3. But, when I change cell B4, it changes the wrong shape. It selects shape 7.2 and not shape 4.1

[abousetta]

Maybe try this approach:

Please Login or Register  to view this content.

abousetta

[abousetta]

Dave, you need to make sure that there are no duplicates or the last one over-rides the previous command.

For example:

fhr - 4
cat - 4.1
fhr - 4.1

The result will be for fhr, not cat.

I hope this helps with the trouble-shooting. The last code I posted seems to work for me. Let me know if you are still having any issues.

abousetta

[Dave G]

Yup, still having weird issues. When I replace my code with yours in the sample schools spreadsheet it will still select the wrong shapes often. For example, if I change cell B47 to FHR, it selects shape object 21.1 for some reason. I'm at a loss as to why.

[abousetta]

Hi Dave,

I might have found the bug...

1) Change the declaration in my code for sh from integer to string

2) Re-code the shapes to be a string like CL-04 instead of an integer like 4. I think the number are throughing it off.

Example:

proposed name CL ZONE 2013 pop
lee CL-04 130
lee CL-4.1 23
cat CL-05 73


This has worked so far in the example above.

Hope this helps.

abousetta

[abousetta]

I am attaching the example with the first few already changed.

abousetta

[Dave G]

Thank you so much for you help! Here's the final code I used

Please Login or Register  to view this content.

[gerfulg]

Hi,
How do I access the codes?