Hi All,
I am attempting to solve a problem of changing the color of an object from a data validation list upon changing the value of the drop-down list.
For example, I have two states, California and Alaska (the actual moveable outline) as objects within excel. When you select a drop-down list you can choose Alaska or California.
When you choose one over the other I have two cells, L8 for Alaska, if selected will turn to an X or be False if not chosen and L9 for California with the same instance.
If either is chosen I have set my code to change it's color.
Option Explicit
Dim obj As Shape
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set obj = ActiveSheet.Shapes("Alaska")
If Range("L8").Value = "X" Then
obj.Fill.ForeColor.SchemeColor = 11
ElseIf Range("L8").Value = "False" Then
obj.Fill.ForeColor.SchemeColor = 1
End If
Set obj = ActiveSheet.Shapes("California")
If Range("L9").Value = "X" Then
obj.Fill.ForeColor.SchemeColor = 11
ElseIf Range("l9").Value = "False" Then
obj.Fill.ForeColor.SchemeColor = 1
End If
End Sub
The problem I am having is that when I select either state from the drop-down list, the color of the state does not change until you actually click off the drop-down list onto another cell.
Automatic calculating is on and even you press F9 while still on the drop-down list it does not refresh the objects.
I am not sure if it is something in my code that is causing the problem or if it is something that excel does poorly with objects.
Any help would be greatly appreciated!
-Bob
Bookmarks