why does this only work on the worksheet_change event?

[rickblunt]

Greetings, I am using this code on a worksheet to change the fill color on a shape dependent on the value in one of the cells. It works great and so I would like to use the same technique in a different WB; but I want to assign it to a command button instead. But I keep getting an error code and I am not smart enough on VBA to understand why....

This works fine:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$46" Then
    '   Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
            If Target.value = 0 Then
                .SchemeColor = 1
            ElseIf Target.value >= 422 Then
                .SchemeColor = 50
            ElseIf Target.value >= 1 Then
                .SchemeColor = 10
            Else
                'it must be less than 1
            End If
        End With
    End If
End Sub

This gives me the "runtime 424 - object required" error... And I cannot figure out why. There is a value in cell U66 and there is a shape called "Rectangle 618" on the sheet. The button is on the worksheet that I am referencing so I am not sure what object it cannot find...

Sub FilterStatus()

If Target.Address = "$U$66" Then
    With ActiveSheet.Shapes("Rectangle 618").Fill.ForeColor
        If Target.value = 0 Then
                .SchemeColor = 1
            ElseIf Target.value >= 422 Then
                .SchemeColor = 50
            ElseIf Target.value >= 1 Then
                .SchemeColor = 10
            Else
                'it must be less than 1
            End If
        End With
    End If
End Sub

I appreciate any assistance at all - thanks, Rick

 

[mikerickson]

In the non-event code, the object Target is not passed to the procedure. You must specify it.

What do you want to happen when the button is pushed?

 

[rickblunt]

Thanks for replying mikerickson. Depending on the numerical value in U66, the rectangle will change colors. if the value is greater than 422 it turns green, from 1 to 422 turns it red, and zero leaves it blank.

 

[mikerickson]

With ActiveSheet
    Select Case Val(Cstr(.Range("U66").Value))
        Case Is = 0
            .Shapes("Rectangle 618").Fill.ForeColor .SchemeColor = 1
        Case Is >= 422
            .Shapes("Rectangle 618").Fill.ForeColor .SchemeColor = 50
        Case Else
            .Shapes("Rectangle 618").Fill.ForeColor .SchemeColor = 10
   End Select
End With

 

[rickblunt]

Thanks for helping me, when I run this new code I get the 438 error (object doesn't support this property or method) error on whatever step the cell value calls for.

 

[rickblunt]

never mind - I just saw that space between the schemecolor and forecolor - it worked great, thanks again for the help