VBA to read cell value rather than the cell formula

[austicks84]

I would like a message box to pop up when a cell in a range changes. The cells in the range all have IF formulas in them. My problem is that the code i am using seems to only read the formula, which doesn't change, hence nothing happens. How can i get my code to read the cell value, which does change, rather than the formula which doesn't?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

 

[pgc01]

From post #1
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

From post #8
This is what's in the target cell:

=VALUE(IF(COUNTIF($P$3:$P$42,R7), 1,2))

Hi

From what I understand from these 2 posts the cell with the formula you posted is one of the cells in the range ED45:EO83 and you want to perform some action when one of these cells changes value.
Since the cell is not modified, the value just changes automatically when the formula is evaluated, the Change event is not fired.

The usual solution to this case is to check which cell is modified.
When a cell with a formula changes value this means that one of its precedents was modified.

You have to analyse the formula and check which precedent is modified and check it with the Change event.

Let me give you the gist of it with a very simple example.

Let's say that in B1 you have the formula =A1+1
You want to execute some code when B1 changes value.
Since B1 has a formula you analyse it and check that the value in B1 changes when you modify the value in A1.
In this case you want to execute a code when the value of B1 changes but you use the Change event on cell A1.

Hope this helps.