Worksheet Change event / Calculate cells changed by formulas

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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?



Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 25, 2012
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
glad you have a solution
(y)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,176
Messages
5,527,255
Members
409,754
Latest member
ekTZ

This Week's Hot Topics

Top