Autoshape color code help

[rickblunt]

Greetings,

I am trying to use the following code to change the color of an autoshape depending upon the value in a cell that it is hyperlinked to. (Rectangle1 is hyperlinked to cell "C46") So if I click on Rectangle1, the cursor is moved to C46, where I type in my value. If the value is less than 601, I need rectangle1 to turn red. Right now nothing happens at all, no errors or anything.

With Worksheets("Inspection Sheet")
        'Change autoshape color to red depending upon cell value
        If .Range("C46").Value <= 600 Then
            ActiveSheet.Shapes("Rectangle1").Fill.ForeColor.SchemeColor = 10
 
        End If
    End With

Thanks for the help, I am still pretty much a noob, there is so much to learn...

 

[Kenneth Hobson]

If you want to automate it, right click the sheet's tab, View Code, and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$C$46" Then Exit Sub
    'Change autoshape color to red depending upon cell value
  With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
    If Target.Value <= 600 Then
        .SchemeColor = 10
        Else: .SchemeColor = 1
    End If
  End With
End Sub

Notice that I changed the name of your shape to the default name for the first one.

 

[rickblunt]

Cool, thanks Kenneth - it worked like a champ.