Worksheet Change event / Calculate cells changed by formulas

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
101
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
glad you have a solution
(y)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top