data validation not working if formula triggered?

Hello,

Newbie on this forum with a question:


Goal is to get a remark/warning if a double value is added in a automated excelsheet (values in sheet are added only through macro's)

I tried to use data validation for the remark and a countif function to find doubles.
Extra difficulty:
Before the potential double value is added, a new row is added/inserted to the sheet, which seems to alter the original formula (even with $).
I managed to get a formula in the sheet itself that does locate double values, but when I try the same thing in data validation, I don't get the remark/warning.
Is data validation not working because it's formula-triggered?

Hope someone can help me with this one