Worksheet Change Stops Working After So Many Rows

Question

The below procedure will compare a range in Sheet1 with a range in Sheet2 based on the value in column A and, if the active cell includes a matching value, it highlights the active cell. It works great! At least up until I enter values from approximately row 890 in Sheet2 and on. In these instances, when I enter a value that should highlight, nothing happens. No error, the highlight simply doesn't occur. Right now, Sheet2 has approx. 1200 lines of data. What am I missing here? Thanks much for any insight!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("V2:Y1500")) Is Nothing Then
        Dim cRow, cPID, lRow As Long
        Dim cVal As String
        cVal = Cells(Target.Row, Target.Column).Value
        cRow = Target.Row
        
        Sheet1.Cells(cRow, Target.Column).Interior.Color = xlNone
        cPID = ActiveSheet.Range("A" & cRow).Value
        lRow = Sheet2.Range("A1500").End(xlUp).Value
        For i = 2 To lRow
            If Sheet2.Range("A" & i).Value = cPID Then
                For j = 12 To 29
                    If Sheet2.Cells(i, j).Value = cVal And Sheet2.Cells(i, j).Value <> "" Then
                        Sheet1.Cells(cRow, Target.Column).Interior.Color = 65535
                        Exit Sub
                    End If
                Next j
            End If
        Next i
    End If

End Sub

This might be the issue: `lRow = Sheet2.Range("A1500").End(xlUp).Value` should be `lRow = Sheet2.Range("A1500").End(xlUp).Row` [i.e. `.Value` -> `.Row`]. Also consider changing `"A1500"` to `"A" & Rows.Count` — Super Symmetry, Nov 29 '20 at 06:47
The line `cVal = Cells(Target.Row, Target.Column).Value` will raise an error if you change more than 1 cell at once. — Super Symmetry, Nov 29 '20 at 06:50
That's got it, @Super Symmetry. Many thanks for the working solution and great feedback! — katech725, Nov 29 '20 at 07:23
I would recommend reading [Working with Worksheet_Change](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Nov 29 '20 at 07:29

Worksheet Change Stops Working After So Many Rows

0 Answers0