Tools / Macro / Record New Macro
can give you some insights, but what you are seeking to do is not
trivial to learn via the macro recorder.

If you ahve time, work through Microsoft Excel N VBA Step by Step, by
Reed Jacobson. Then if you get stuck on a particular point, try the
Programming newsgroup.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup