Conditional formatting: SUMPRODUCT over range - delete rows

[ExcelStefan]

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!!

[martindwilson]

OR(SUMPRODUCT(($A$1:$A$1000=$A4) does not change when i delete rows
but if you want you could use
$A$1:INDEX($A:$A,1000) instead of $A$1:$A$1000

[ExcelStefan]

Hi Martin,

Thanks for your reply. Huh? When I delete rows, the conditional formatting for the columns changes (see attachement).

[martindwilson]

hmm i tested with excel97 maybe thats it, but the index should fix it