VBA won't execute when formula changes cell value

Hi all,

I'm having a hard time getting this to work;

VBA executes the code when value of a cell is "X"

When the value of a cell is changed to "x" due to a formula (=IF(A3+A5=4,"x",0)), unfortunately VBA will not be triggered unless I manually put an 'X' into the cell.

Can any one help me out with this?


the code is use:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim r As Range

 Application.EnableEvents = False
 If LCase(Target) = "x" Then
 With Sheets("mail")
 LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
 Target.EntireRow.Copy Destination:=.Cells(LR, 1)
 Target.EntireRow.Delete
 End With
 End If
 Application.EnableEvents = True
 End Sub

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Me.Calculate
 End Sub

Formula does not raise Change event. You may use Calculate event or adapt Change event to look for changes in cells referred to by formula.

I must see workbook to change the code. Calculate event has no Target parameter and so the code must test all the required formula cells, or the Change event must test input cells for changes but I do not know where they are.

You may try this instead of your codes:

Private Sub Worksheet_Calculate()
   Dim rMatch As Range
   Dim r As Range
   For Each r In Intersect(Me.UsedRange, Range("A:A"))
      If r.Value = "x" Then
         If rMatch Is Nothing Then
            Set rMatch = r
         Else
            Set rMatch = Union(rMatch, r)
         End If
      End If
   Next r
   If Not rMatch Is Nothing Then
      Intersect(rMatch.EntireRow, Me.UsedRange).Copy Sheets("DOC").Cells(Rows.Count, "A").End(xlUp).Offset(1)
      rMatch.EntireRow.Delete
   End If
End Sub