Conditional formatting: SUMPRODUCT over range - delete rows

Dear ExcelForum,

Attached is a file which checks duplicate entries with conditional formatting. Duplicates are rows which contain all the same values (case for rows 2 and 3 in attached example) - the second duplicate row is marked red to be deleted.

There are further checks: if information is incomplete (first or last name missing), or if the person is the same but one or both dates have changed, the corresponding rows are marked orange.

There's a third condition, which turns the row green if an "x" is in a row.


My problem: The orange (second) condition is over a range $A$1:$A$1000. When I delete rows (which I do often), the orange condition doesn't stay over the range $A$1:$A$1000, but the range shrinks as rows are deleted.

Does somebody have an idea, how to either freeze the range of the SUMPRODUCT function to $A$1:$A$1000, or how to avoid using a range?

Excel 2003, unfortunately

Thanks!!