Welcome to the board. I'm not going to give you the pox but I'll try and help with your macro...
You need to create the right event to run the code automatically. Ron's piece of code uses the Worksheet_Change event, which runs whenever you change that worksheet. However, your requirement suggests you may not be changing the worksheet, only recalculating it. You may instead be changing a different worksheet or workbook, or incrementing something through a piece of code. Whatever - you need to consider what is triggering the change in this value, and set an event accordingly
Events can include workbook events (workbook open, before save, before close etc) and worksheet events (activate, deactivate, change, selection change, before calculate etc). They can be found as follows:
- open the VB Editor
- activate the worksheet (or workbook) code module by double-clicking
- you'll see 2 dropdowns above your code window. From the (general) dropdown, select Worksheet. If you have no other events set up, then the Worksheet_SelectionChange event is created
- for your purposes you may want the calculate event, so change the second dropdown. You'll see this event created
These pieces of code that are created will run whenever the relevant circumstances arise. If they contain no other code then you wont see anything, but they still run. Have an explore of all the different events available, have a play to see what each does, and decide which one suits you best
You can place your main piece of code in a normal code module and simply call it from the event code.
##WARNING## There is a danger with events, in that you can trigger a terminal loop. For example, if your code runs whenever you change the sheet, and your code itself changes the sheet, it will keep calling itself ad infinitum. Use application.enableevents = false / true to switch the events feature off when you need to prevent this happening
