thank you gentlemen, i appreciate the help.
the first code didn't work for me, but after reading this response,
i'm messing around with the advice and i believe i can make it work.

thanks again,
jasonk


On Fri, 17 Mar 2006 14:10:27 -0800, Tom Ogilvy
wrote:

What will make the value change?

If by editing use the Worksheet_Change event
if by formula, use the Worksheet_Calculate event

You get to these by right clicking on the sheet tab and selecting view code.

In the resulting module, in the dropdowns at the top
Left: Worksheet
Right: Change (or Calculate)

this places an event procedure definition in the module.

In the Change event, the Target argument contains a reference to the cell
that triggered the change (was edited usually). Calculate doesn't give you
that information.

Any cell that is changed by editing will trigger the Change event. So in
the event, you put code to determine whether to react or not

With calculate, you will need to determine whether you need to act or not.
If it is something to do over and over, then no problem. But if you only
want to do it once when the value changes, you will need to store information
- perhaps in static variables.

See Chip Pearson's page on events:

http://www.cpearson.com/excel/events.htm