Naavy,
I also use Excel 97 so that shouldn't be the problem.
Error 424 (Object Required) basically means that an unqualified object is being referenced (i.e., Excel doesn't know what the object is.)
As long as you've pasted this into the Workbook's code module (typically named "ThisWorkbook" in the VB Editor), it should work with no problems.
The only other thing I can suggest is to go the the Workbook's code module, pick "Workbook" from the Object menu (top left drop-down in the code view window) and pick "SheetChange" in the Procedure menu (top right drop-down in the code view window) and make sure that the objects being passed to the procedure by Excel are the spelled the same as in the code snippet.
If you want, email the sheet to me:
peter.hopkins@convergys.com
and I'll try to get it working and send it back. If you have any sensitive data in the workbook, please replace it with sample data that meets the specified criteria of the sheet (but is NOT sensitive) before sending it to me.
Should you choose the latter option, please be aware that while I won't be back in the office (or on Tek-Tips for that matter) until 5/28, I will be happy to work on your workbook upon my return.
Hope this helps,
Pete