Worksheet Change event / Calculate cells changed by formulas

szita2000

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,297
Office Version
365
Platform
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
68
Office Version
365
Platform
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,297
Office Version
365
Platform
Windows
glad you have a solution
(y)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,850
Messages
5,483,303
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top