Posts by leochngyh

  • Runtime Error 13 after copy paste entire colume

    Hi,
    I am creating a macro to update a round shape before my cell to indicate it it on/off target. First time posting in this forum, Apologies if I make any errors or break the forum rule.





    The macro run smoothly until I copy paste the entire column of data from the raw file and paste into the macro, then Runtime Error 13 pop out.



    The debugging is pointing on [If Target.Value < Inflation_Target Then]





    Private Sub Worksheet_Change(ByVal Target As Excel.Range)



    Inflation_Target = Worksheets("KPI").Range("B9")
    Inflation_Alert = Worksheets("KPI").Range("C9")



    If Not Intersect(Target, Range("D9")) Is Nothing Then
    If Target.Value < Inflation_Target Then
    ActiveSheet.Shapes("InflationOval0").Fill.ForeColor.RGB = vbGreen
    ElseIf Target.Value >= Inflation_Target And Target.Value < Inflation_Alert Then
    ActiveSheet.Shapes("InflationOval0").Fill.ForeColor.RGB = vbYellow
    Else
    ActiveSheet.Shapes("InflationOval0").Fill.ForeColor.RGB = vbRed
    End If
    End If

    If Not Intersect(Target, Range("E9")) Is Nothing Then
    If Target.Value < Inflation_Target Then
    ActiveSheet.Shapes("InflationOval1").Fill.ForeColor.RGB = vbGreen
    ElseIf Target.Value >= Inflation_Target And Target.Value < Inflation_Alert Then
    ActiveSheet.Shapes("InflationOval1").Fill.ForeColor.RGB = vbYellow
    Else
    ActiveSheet.Shapes("InflationOval1").Fill.ForeColor.RGB = vbRed
    End If
    End If



    End Sub



    Can somebody help me on this?



    I tried to search in google and also in this forum but couldn't find solution.
    The post in "http://www.ozgrid.com/forum/newthread.php?do=newthread&f=158" is not solving my problem as I am refreshing the target cell when pasting the data.





    Thanks
    Leo