0

I am trying to detect if there are changes in a cell value, not particularly the cell contents. I have found multiple solutions to find out if a cell contents has changed, but it does not work when a cell is equal to another cell.

For example, I have cell A1 set to equal B1 and then B1 has a formula that calls in multiple other cells, so I am not able to go back to the beginning and determine whether the cell has changed from that. It needs to come directly from A1.

This is one of the examples I found on this site, but does not determine if the value of A1 has changed, just whether the contents has changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
     Cells(Target.Row, 3).Value = Date
End If

End Sub
matt
  • 1
  • 2
  • 2
    Have you seen [This](http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula/11409569#11409569)? – Siddharth Rout Jul 23 '15 at 19:37
  • The `Worksheet_Change` event macro is not triggered when a value returned by a formula changes. –  Jul 23 '15 at 20:13

1 Answers1

0

The function application.volatile TRUE at the top of your sub will make your sub calculate each time any value in Excel changes. So then you need a global variable which stores the last-known value of your specified range, and any time the sub runs, start with an 

If new_cell_value <> stored_global_variable then...

and close with 

    stored_global_variable = new_cell value'
End If

See here for further info [h/t to vzczc for the original answer and method]: Refresh Excel VBA Function Results

Community
  • 1
  • 1
Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
  • It still doesn't work between sheets. If I change something on sheet 1 that causes changes on sheet 2, it doesn't work. Is there a way to fix that? – matt Jul 27 '15 at 18:33
  • Could you post your changes to your code (edit into your original q) and an example of the data used to get a failing result? – Grade 'Eh' Bacon Jul 27 '15 at 18:58