The short answer? When no more bugs are found and you've stopped writing code, or when the code stops being used.

I find there are two modes for writing tests: proactive and reactive. In reactive testing you write a test when a bug is found (and before it is fixed) that fires for the buggy code and succeeds when the bug has been fixed.

Proactive testing checks against coding issues, edge cases in the data and unusual interactions with the user interface. The data tests are generated by code inspection and knowledge of the problem domain. The interaction tests are generated by inspecting the UI and by doing "silly stuff" (idiot proofing tests).

Proactive tests tend to be written as the code is written (before if you are writing test driven code) and reactive tests are obviously written after the code. Reactive tests may often be written to further test issues found by proactive tests.

Writing proactive tests tends to finish when you have finished writing the code. Reactive test writing finishes when bugs stop being detected.