Count on cell for each time it is changed

J

Jelinek

I would like to count the number of times a cell has been amended/ changed

e.g cell b2 has the word red it it, in cell c2 i would like a counter for
everytime the cell in b2 has changed, at the end of the day you get final
number. Is this at all possible?
 
A

Art

Jelinek,

You can do it by putting the following VBA macro in your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Cells(2, 3) = Cells(2, 3) + 1
End If
End Sub

Art
 
J

Jelinek

Art,

I am only used to working with very basic macos, how do i get this Macro to
work, i tried cutting and pasteing the code putting into a module in visual
basic editor, but does not seem to do anything?

What am i doing wrong?

Thanks

Simon
 
A

Art

Jelinek,

This particular macro needs to be in the module assigned to the sheet you're
working on. If you look in the VB editor, you should see a list of sheets
underneath Excel Objects. Choose the sheet that you're working with and
double click it. You should get a window with 2 drop down boxes at the top
-- one says General and one says Declarations.

Drop down the one that says General and select Worksheet instead. It will
create a "shell" for a macro -- that you don't want. Now go to the drop down
on the right and select "change" instead of "selection change". You can now
delete the "selection change" one that was created.

You'll see that the top line of what was now created is the same as the top
line of the macro that I sent to you. Fill in the rest of it. With any luck
this should work.

Art
 
Top