We have a straightforward opinion here: VBA is not the solution for this problem, and it never really was. The user Dep517 wants a running log of who is assigned to a post as people rotate off, stored on a separate audit sheet. They asked for VBA help because that is the default answer in the spreadsheet community when someone says "automate." But the real solution is simpler, more human, and far less fragile. A change tracker built on VBA requires the user to trust a macro they cannot read, maintain code they did not write, and hope the workbook does not break when someone else opens it. That is not empowerment; it is dependency.
What Dep517 actually needs is a human-readable change log that works transparently. Instead of a hidden macro that fires on cell changes, consider a dedicated input table on the audit log sheet where users enter a date, the post name, and the person's name. This is a manual step, but it is also an intentional one. It forces accountability and creates a record that anyone can audit without needing to understand VBA. For those who still want automation, a simple formula using `IF` and `TODAY()` can flag when a post assignment changes, prompting a manual entry. The goal is not to remove human effort entirely; it is to make the effort meaningful and the data trustworthy.
The deeper issue here is how we think about automation in spreadsheets. The assumption is that any repetitive task must be coded away. But the most effective tools are the ones that respect the user's context. A post rotation log is a shared document, often maintained by people with varying technical comfort levels. VBA introduces a barrier to entry. A well-designed input form, a clear naming convention, and a simple table do not. They invite participation rather than requiring expertise.
Our advice to Dep517 is to step back from the code and look at the workflow first. Ask who will use this log, how often, and what they need to see. Build the audit log as a plain table with columns for timestamp, post identifier, outgoing person, and incoming person. If the team finds that too burdensome, then explore Power Query or Excel's built-in data validation tools before touching VBA. The spreadsheet should serve the people using it, not the other way around. Start with the simplest version that works, and only add complexity when the simple version proves insufficient.