Worksheet Change event / Calculate cells changed by formulas

szita2000

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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Yongle

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,540
Messages
5,511,920
Members
408,868
Latest member
AndrewLeonard

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top