Change color of shapes based on a value on another sheet

I have a macro that changes the color of shapes, based on a value on another sheet.

I have uploaded a test file.

The thing is that the colors don't change at all. However if I put a cell anywhere on sheet "Draft" and refer it as =Table!A2 and another one =Table!A3 - the macro starts working right away. Although they are not referred anywhere in the macro. When I delete them - macro stops working.

Any idea why is that?

Thank you in advance!

sorry the file is not readalbe
can u check it out and upload again
when i open error comes

all right, try now!

Here is the actual macro:


Private Sub Worksheet_Calculate()

Dim rng1 As Range, rng2 As Range

Set rng1 = Worksheets("Table").Range("A2")
Set rng2 = Worksheets("Table").Range("A3")

Dim shp1 As Shape, shp2 As Shape
Dim bytColor1 As Byte, bytColor2 As Byte

Set shp1 = Worksheets("Draft").Shapes("Rectangle1")
Set shp2 = Worksheets("Draft").Shapes("Rectangle2")

Select Case rng1
Case 1 To 1.99
bytColor1 = 2
Case 2 To 2.99
bytColor1 = 3
Case 3 To 3.99
bytColor1 = 4
Case 4 To 4.99
bytColor1 = 5
Case 5 To 5.99
bytColor1 = 6
Case Else
bytColor1 = 1
End Select
Select Case rng2
Case 1 To 1.99
bytColor2 = 2
Case 2 To 2.99
bytColor2 = 3
Case 3 To 3.99
bytColor2 = 4
Case 4 To 4.99
bytColor2 = 5
Case 5 To 5.99
bytColor2 = 6
Case Else
bytColor2 = 1
End Select
shp1.Fill.ForeColor.SchemeColor = bytColor1
shp2.Fill.ForeColor.SchemeColor = bytColor2
End Sub