Excel 2007 : VB doesn't read the data from the cells in excel.Help!!

VB does not read the data from the cell which is imported automatically via excel formula



I wrote the following code, to change the colour of the autoshape based on the entered number into cell B42, which it works fine when I manually type in 1,2 or 3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B42")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value = 1 Then
ActiveSheet.Shapes("Freeform 377").Fill.ForeColor.RGB = vbGreen
ElseIf Target.Value = 2 Then
ActiveSheet.Shapes("Freeform 377").Fill.ForeColor.RGB = vbYellow
ElseIf Target.Value = 3 Then
ActiveSheet.Shapes("Freeform 377").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub

But B42 actually shows result of a calculation. In the other word this cell shows the result of the formula which is assigned to it.

The problem is the data which goes to B42 are still 1,2 or 3 which are automatically generated by the excel formula but when data goes to cell B42 automatically rather than Manually VB doesn't run and colours do not change.

Does anyone know how to fix it? or is there anyway to put a button to refresh the cell?

I would appreciate if someone help me with that.

Thanks,
Dave