Counting Value Changes in Excel

**MrReagan8** · 04-12-2013, 12:03 PM

Hi there,

I have been searching for this answer in previous threads, but have had no luck.

I am trying to track changes to a cell value. I have several hundred line items and each time a value in column A (A3 for example) changes, I would like to have a separate column display how many times that cell has changed.

Let me know if any more information is needed.

Any help is appreciated. Thank you in advance!

**HaHoBe** · 04-12-2013, 05:38 PM

Hi, MrReagan8,

the most interesting part woul dbe to know how the cell changes it´s value: by an entry from the user, by a formula or via a query. If the change is done manually you may use the Worksheet_Change-Event to fire whenever any change is made in Column A of that Sheet (right-click on the worksheet Tab, View Code):

Please Login or Register  to view this content.

If the changes are not done manually please tell how this is done.

Ciao,
Holger

**MrReagan8** · 04-12-2013, 06:17 PM

Thank you for the response. The changes are done manually and by the user. I have used a few codes in the past, however they have not accomplished exactly what I would have liked. I specifically need a code to track the amount of times the cell value changes. For example, say cell A1 changes from 10 to 20. I would like cell B1 to track the change and have a value go from 0 to 1. Then when cell A1 changes from 20 to 30, I would like B1 to update from 1 to 2.

Does this make sense?

Any help is appreciated!

**HaHoBe** · 04-13-2013, 12:57 AM

Hi, MrReagan8,

you would have to change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

for the column in which to show the number of changes.

The code is fired on any change in the worksheet. First check is for only one cell as with this code there would be a run time error if multiple cells are altered at the same time (code must be changed to loop through the cells which are changed but as I understood it from the description I would change cells one on one). If it´s only one cell the range for the macro is narrowed down to just Column A. With this code you would need to enter zeros if you want to have them available, here it´s blanks for no changes so far. The value in the given cell is augmented by one while dis- and enabling the code for the change (it would get fired a second time for a change which is not in the column that should start the event). So as far as I understood the problem the code should do what you want.

Ciao,
Holger