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

  • Re: Runtime Error 13 after copy paste entire colume


    The problem is probably because target is more than one cell.


    If you want the code to run when you paste in more than one cell you will have to loop through the cells.


    If you don't want the code to run when you paste in a bunch of cells you need to use target.count to see if there is more than one cell that changed and exit the procedure.


    Something like this on the line below the title line.

    Code
    If Target.Count > 1 Then Exit Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!