Select case statement - it will not work and i dont understand why! :o(

Hi NG,

Can anyone tell me what i am doing wrong in the below code?
In the first version i was actively selecting the rectangle, and it was
working. I changed it to a with - statement because it was pretty
annoying that the rectangle constantly was selected.
But now?

thx in advance
Thomas

--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Number
Number = Range("a1").Value
Select Case Number
Case 1
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 44
.Transparency = 0.8
End With
Case 2
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 34
.Transparency = 0.5
End With
Case 3
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 24
.Transparency = 0.3
End With
Case Else
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
Visible = msoFalse
End With
End Select
End Sub

this worked for me:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Number
Number = Range("a1").Value
Select Case Number
Case 1
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 44
.Transparency = 0.8
End With
End With
Case 2
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 34
.Transparency = 0.5
End With
End With
Case 3
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 24
.Transparency = 0.3
End With
End With
Case Else
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoFalse
End With
End Select
End Sub

--
Regards,
Tom Ogilvy
"Thomas" <sardo@nospam.dk> wrote in message
news:1127748980.073459.12310@f14g2000cwb.googlegroups.com...
> Hi NG,
>
> Can anyone tell me what i am doing wrong in the below code?
> In the first version i was actively selecting the rectangle, and it was
> working. I changed it to a with - statement because it was pretty
> annoying that the rectangle constantly was selected.
> But now?
>
> thx in advance
> Thomas
>
> --------------------
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim Number
> Number = Range("a1").Value
> Select Case Number
> Case 1
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 44
> .Transparency = 0.8
> End With
> Case 2
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 34
> .Transparency = 0.5
> End With
> Case 3
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 24
> .Transparency = 0.3
> End With
> Case Else
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> Visible = msoFalse
> End With
> End Select
> End Sub
>

And for me too. Great!

Thank you

try this which will change the shape when you change cell a1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target
Case 1: x = 4
y = 0.8
Case 2: x = 5
y = 0.5
Case 3: x = 6
y = 1
Case Else
End Select
With Worksheets(1).Shapes(1).Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = x
.Transparency = y
End With
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Thomas" <sardo@nospam.dk> wrote in message
news:1127748980.073459.12310@f14g2000cwb.googlegroups.com...
> Hi NG,
>
> Can anyone tell me what i am doing wrong in the below code?
> In the first version i was actively selecting the rectangle, and it was
> working. I changed it to a with - statement because it was pretty
> annoying that the rectangle constantly was selected.
> But now?
>
> thx in advance
> Thomas
>
> --------------------
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim Number
> Number = Range("a1").Value
> Select Case Number
> Case 1
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 44
> .Transparency = 0.8
> End With
> Case 2
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 34
> .Transparency = 0.5
> End With
> Case 3
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> .Visible = msoTrue
> .Solid
> .ForeColor.SchemeColor = 24
> .Transparency = 0.3
> End With
> Case Else
> With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
> Visible = msoFalse
> End With
> End Select
> End Sub
>