A macro is the only way, yes. Some questions:
- So user enters 1 in A1, 2 in A2
- User then enters 3 in A3 and it clears the value in A1
- Are you looking for any value, or only where the entry matches the row number?
- What happens if a user enters data in A4? Will it clear A2?
- What happens when they enter data in A1 after the above scenario... does it clear A3 or A2? What is the reason for your selection?