Hey guys,

This is probably something easy but I can't figure it out. I have this macro to color shapes depending on the value

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = [T5]
Set rng2 = [U5]
Set rng3 = [V5]

If Intersect(Target, Union(rng1, rng2, rng3)) Is Nothing Then Exit Sub

Dim shp1 As Shape, shp2 As Shape, shp3 As Shape
Dim bytColor1 As Byte, bytColor2 As Byte, bytColor3 As Byte
Set shp1 = ActiveSheet.Shapes("Oval 38")
Set shp2 = ActiveSheet.Shapes("Oval 47")
Set shp3 = ActiveSheet.Shapes("Oval 65")

Select Case rng1
    Case 57 To 100
        bytColor1 = 2
    Case 26 To 56
        bytColor1 = 5
    Case 1 To 25
        bytColor1 = 3
    Case Else
        bytColor1 = 1
End Select
Select Case rng2
    Case 68 To 100
        bytColor2 = 2
    Case 34 To 67
        bytColor2 = 5
    Case 0 To 33
        bytColor2 = 3
    Case Else
        bytColor2 = 1
End Select
Select Case rng3
    Case 0 To 25
        bytColor3 = 2
    Case 26 To 56
        bytColor3 = 5
    Case 57 To 100
        bytColor3 = 3
    Case Else
        bytColor3 = 1
End Select
shp1.Fill.ForeColor.SchemeColor = bytColor1
shp2.Fill.ForeColor.SchemeColor = bytColor2
shp3.Fill.ForeColor.SchemeColor = bytColor3
End Sub
Works fine when I enter values from 1 to 100 at T5, U5, V5. However these cells should be formulas and when I change them, coloring stops working.(for example this is the formula for T5: =IFERROR((($T$2/$T$3)*6.25)/100; "")

How do I fix this?

Thank you!