conditional format shape in a diffrent sheet

[Michael S.]

Hi there

I have a workbook with 2 sheets. in the first sheet (sheet1) i have embeded
a textbox. Also in the first sheet there is a cell which shows a result of a
formula (the result can be 1, 2 or 3).

In the second sheet (sheet2) there is a shape (rectangle 1).

Now the shape should change its color while typing in the textbox in
condition to the result of the formula mentioned above.

If the shape is in the same sheet as the textbox, there is no problem to
realize my goal. As soon as the shape is in a different sheet, the following
code does not work. Can anybody help me please?

Thanks
Michael

Private Sub TextBox1_Change()

If Sheets("sheet1").Range("A1") = "1" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 50 'green

End If

If Sheets("sheet1").Range("A1") = "2" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 'red

End If

If Sheets("sheet1").Range("A1") = "3" Then

Sheets("sheet2").Shapes("rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13 'yellow

End If
End Sub

 

[Don Guillett]

try this. modify your sheet and range to suit
Private Sub TextBox1_Change()
Select Case Sheets("sheet4").Range("g1")
Case 1: mc = 50
Case 2: mc = 10
Case 3: mc = 13
Case Else
mc =10
End Select
Sheets("sheet6").Shapes("rectangle 1") _
..Fill.ForeColor.SchemeColor = mc
End Sub