Cell data based on autoshape color

[T-bone]

Hi all

I am using the formula below to assign a number to a cell based on the color
of an autoshape (oval).
My problem is that i have multiple autoshapes on the same sheet. I need each
autoshape to assign a number to a corresponding cell.
When i try to just copy the formula and change autoshape name and cell
number, i get an error message.
I assume you can't have two Worksheet_SelectionChange.
Please help me

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Consolidation").Shapes("Oval 1").Fill.ForeColor.SchemeColor = 11
Then
Range("A1").Value = 1
ElseIf Sheets("Consolidation").Shapes("Oval 1").Fill.ForeColor.SchemeColor =
34 Then
Range("A1").Value = 2
ElseIf Sheets("Consolidation").Shapes("Oval 1").Fill.ForeColor.SchemeColor =
10 Then
Range("A1").Value = 3
Else: Sheets("Consolidation").Shapes("Oval 1").Fill.ForeColor.SchemeColor = 10
Range("A1").Value = 4
End If

End Sub


-T

 

[Ken]

T
You can't have two Worksheet_SelectionChange Events, but you can have
one that does two things. I think you should look into the Select
Case statement, and see if you can get something like this to do what
you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Sheets("Consolidation").Shapes("Oval
1").Fill.ForeColor.SchemeColor
Case 11
Range("A1").Value = 1
Case 34
Range("A1").Value = 2
Case 10
Range("A1").Value = 3
Case Else
Range("A1").Value = 4
End Select

Select Case Sheets("Consolidation").Shapes("Oval
2").Fill.ForeColor.SchemeColor
Case 11
Range("A2").Value = 1
Case 34
Range("A2").Value = 2
Case 10
Range("A2").Value = 3
Case Else
Range("A2").Value = 4
End Select


End Sub


Good luck.

Ken
Norfolk, Va

 

[T-bone]

Cheers Ken
That was spot on!
thanks

T

T
You can't have two Worksheet_SelectionChange Events, but you can have
one that does two things. I think you should look into the Select
Case statement, and see if you can get something like this to do what
you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Sheets("Consolidation").Shapes("Oval
1").Fill.ForeColor.SchemeColor
Case 11
Range("A1").Value = 1
Case 34
Range("A1").Value = 2
Case 10
Range("A1").Value = 3
Case Else
Range("A1").Value = 4
End Select

Select Case Sheets("Consolidation").Shapes("Oval
2").Fill.ForeColor.SchemeColor
Case 11
Range("A2").Value = 1
Case 34
Range("A2").Value = 2
Case 10
Range("A2").Value = 3
Case Else
Range("A2").Value = 4
End Select


End Sub


Good luck.

Ken
Norfolk, Va