Worksheet Change event not working unless cell is manually changed

[Semper]

Hey
First of all I have no idea about VBA and Macros. Normal Excel formulas are not a problem, but VBA is a number too high for me.:( That's why I hope for your help.

I have the following problem:

A value is written using a formula in cell A1 (example: A1 =C1+D1)
a random number should then be generated in B1.
The number generated in B1 must not change unless A1 changes.
and all from A1:A1000

With the code it only works if you type in the number manually. But not if an external source or the value is output from a formula.



Private Sub Worksheet_Change(ByVal Target As Range)
Set rngIntersect = Application.Intersect(Range("A1:A1000"), Target)
If Not rngIntersect Is Nothing Then
For Each cell In rngIntersect
cell.Offset(0, 1).Value = IIf(cell.Value <> "", Int((55 - 25 + 1) * Rnd + 25), "")
Next
End If
End Sub


I am unable to rewrite the code to generate the random number when a formula returns a value.

I found this article and tried to rewrite the code myself but I only get error messages.
https://superuser.com/questions/1298289/excels-worksheet-change-event-not-firing-unless-cell-is-manually-changed

Hope someone can help me: pray2:

[Aussiebear]

One of the answers given on that webpage suggests that "If the Range is Manually Refreshed then the Change Event can be used. If the Range contains any Formula, then the Calculate Event can be used." Did you try this?