Worksheet Change event / Calculate cells changed by formulas

[szita2000]

Hi guys.

I am struggling with this one.

So far I had this code to test my color changing shapes.
I created a small table with
Shape name, Value, Target Value
It was working whenever I manually typed over the value.

But now I have to change the code so the value triggers the macro coming from a formula.

How can I modify my code so at the recalculation the ranges being evaluated against each other and triggering the color change in my shapes?

Private Sub Worksheet_Change(ByVal Target As Range)Dim myText As String
Dim myPercentage As String
Dim switch As Boolean
Dim myShape As Object
Dim test As String


'Look for change in the range
    If Not Intersect(Target, Range("X17:X26")) Is Nothing Then




        'The target score we testing against
        test = Target.Offset(0, -1).Value
        
        Set myShape = ActiveSheet.Shapes.Range(Array(test))


        myShape.TextFrame.Characters.Text = Target.Offset(0, -1).Value & vbLf & Format(Target.Value, "0%")


        If Target.Value < Target.Offset(0, 1).Value Then
        myShape.Fill.ForeColor.RGB = RGB(255, 13, 13)
        'An exception as I couldn't find the right polygon so two triangles have to mimic to be a polygon
        ActiveSheet.Shapes.Range(Array("Agility2")).Fill.ForeColor = ActiveSheet.Shapes.Range(Array("Agility")).Fill.ForeColor
        ActiveSheet.Shapes.Range(Array("Stability2")).Fill.ForeColor = ActiveSheet.Shapes.Range(Array("Stability")).Fill.ForeColor


        Else


        myShape.Fill.ForeColor.RGB = RGB(146, 208, 80)
        ActiveSheet.Shapes.Range(Array("Agility2")).Fill.ForeColor = ActiveSheet.Shapes.Range(Array("Agility")).Fill.ForeColor
        ActiveSheet.Shapes.Range(Array("Stability2")).Fill.ForeColor = ActiveSheet.Shapes.Range(Array("Stability")).Fill.ForeColor


        End If


    End If


End Sub

My guess that I would use the worksheet calculate event and just call this as a macro so technically the macro called all the time evaluating all the cells in the range. I would probablyloop through the range comparing the value with the target.
Thanks

 

[Yongle]

Your trigger range should be modified
It needs to be the cell(s) being manually changed (instead of the cell(s) containing the formula
But (naturally) the resultant change is dependant on whatever is returned from the formula

 

[szita2000]

Hi Yongle.

Thanks for the quick reply.
This was my issue, that the trigger range is an array on another sheet.

I managed to solve it: A calculate event on the sheet that is being changed, triggers the macro above (that I put in a module).
It works so far.

Thanks

 

[Yongle]

glad you have a solution