Worksheet Change event / Calculate cells changed by formulas
Results 1 to 4 of 4

Thread: Worksheet Change event / Calculate cells changed by formulas

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet Change event / Calculate cells changed by formulas

    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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,593
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Worksheet Change event / Calculate cells changed by formulas

    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

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Change event / Calculate cells changed by formulas

    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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,593
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Worksheet Change event / Calculate cells changed by formulas

    glad you have a solution
    Last edited by Yongle; Jul 15th, 2019 at 08:25 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •