VBA not updating when cell value changes

[cjcass]

Hi,

I have some code in my worksheet (entered by right clicking wksheet tab and entering code) as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$8" Then
Select Case Target.Value
Case Is = "Full"
ActiveSheet.Shapes("Rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 118, 115)
Case Is = "Empty"
ActiveSheet.Shapes("Rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 158, 154)

But when I copy & paste a new value into my target cell ($E$8), the vba doesn't detect the change and update Rectangle 1's colour...

Any ideas on how I can automate this?

Many thanks,
Chris

 

[mumps]

Try:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$8" Then
        Select Case Target.Value
            Case "Full"
                ActiveSheet.Shapes("Rectangle 1").Select
                Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 118, 115)
            Case "Empty"
                ActiveSheet.Shapes("Rectangle 1").Select
                Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 158, 154)
        End Select
    End If
End Sub

 

[cjcass]

Hi,

Have tried that and it didn't seem to work?

Rgds