| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell data based on autoshape color
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data based on autoshape color
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 On Oct 28, 8:58�pm, T-bone wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell data based on autoshape color
Cheers Ken
That was spot on! thanks T "Ken" wrote: 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 On Oct 28, 8:58�pm, T-bone wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically change autoshape color to that of another autoshape | Excel Programming | |||
| Hide Autoshape based on Cell Value | Excel Programming | |||
| fill autoshape color with ref cell conditional formatting | Excel Worksheet Functions | |||
| How to sum data based on font or cell color? | Excel Worksheet Functions | |||
| Put an autoshape in a cell based on another cells content | Excel Worksheet Functions | |||
Linear Mode
