Cell change report

[habil]

I want to know Cell A1 how many times change in Cell B1.
Note: Cell A1 contains formula. I will do not input manually in A1.

Please advise me. Thanks in advance.

 

[Yongle]

The value in A1 increments by 1 when the value in B1 changes...

Place code in Sheet module (right-click sheet tab \ view code \ paste in module window)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then Range("A1").Value = Range("A1").Value + 1
End Sub

 

[habil]

Thanks for your response. But if B1 contains formula ( say B1 =B5+65) then it is not work.

I means when I change B5, then B1 updated but A1 still same. no change in A1.

 

[Yongle]

If B5 is the cell amended by you, and formula in B1 is =B5+65 , then base the calculation on B5 changes...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Then Range("A1").Value = Range("A1").Value + 1
End Sub

 

[Peter_SSs]

Playing the Devil's advocate ..

Not sure how pedantic the OP wants to be but ..

a) If B5 houses a 5 and then has 5 re-entered in it, the code will increase the value in A1, yet the value in B1 has not actually changed (question was "how many times change in Cell B1")

b) B5 can be changed without Target.Address being "$B$5" so A1 could miss increasing when B5, and hence B1, changes.

 

[Yongle]

@Peter_SSs
@habil - please let us know exactly what needs counting

thanks

 

[habil]

If cell A1 contains formula A1 =Today(),

Than I want to record How many times system date change. Even If I system date changed to back dated manually also I want to record.in cell B1.



Everyone highly requested to solve my problem.

Thanks in advance.