I had an entirely different approach. I'm confident that you know a
great deal more about this than I do, but perhaps you could tell me
what's wrong with my thinking here. I'm trying to understand the
perspective of a seasoned programmer on the many ways of skinning
Excel cats.

I would have created buttons that set an arbitrary cell to 1 or 2,
then put formulae in each of the target cells on "Products"
referencing this cell and setting a value equal to Retail Price or
Cost Price depending upon whether it is 1 or 2.

This would allow for dynamic changes to the Prices, and more
importantly, would be easier to change next year when a third column
is added and the person who wrote the first spreadsheet has left
town, abandoning the work to someone who knows no VBA.

Whaddya think?