The reason for error is because once you delete the row number shifts..

Try the below version.If you are looking to delete these rows then you dont
need a formula to be assigned...You can validate that in code and delete the
row

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If Sheets("DataInput").Range("A1") = Range("A" & lngRow) And _
Range("E" & lngRow) = "Spot Deal" Then Row(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

Following code was adopted from thread and modified to select rows with €œY€�
in column I. The problem is not all rows with €œY€� are deleted when range of
rows has found €œY€� (say row 10 to 16 all has Y), it is fine if single row is
found with €œY€�.
Sheets("Data").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("I1").Formula = "=if(and(RC[-8]=DataInput!R1C1,RC[-4]=""Spot
Deal""),""Y"",""N"")"
Range("I1").Copy
Range("I1:I" & lastrow).Select
ActiveSheet.Paste
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
Set myrange = Range("I2:I" & lastrow)
For Each Count In myrange
If Count.Value = "Y" Then
Count.EntireRow.Delete
End If
Next