Trigger Macro when Cell>Another Cell

[brumby]

Hiya all,

I am trying to trigger a macro as soon as a cell in a range becomes greater than a corresponding cell within another range, eg below

Range k:K is the "original" range, this is what the cells within AU:AU are comparing to,

As soon as a cell in range AU:AU is >= the same cell in K:K copy and paste AU:AU as value into K:K see below example on k15 & au15

K15 = 100 & AU15 = 75 - do nothing
k15 = 100 & AU15 = 99 - do nothing
k15 = 100 & AU15 = 100 - make k:K = au:au as hard value.


Is this possible?

Many Thanks

 

[Michael M]

How does the cell in Au change ??
Is it by formula, manual input ??

 

[brumby]

Hi there, by formula

 

[Michael M]

So do you manually imput the precedent cell that triggers the formula ??

Try this in the sheet module

Sub worksheet_calculate()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
For r = 1 To lr
If Range("AU" & r).Value >= Range("K" & r).Value Then
    Range("K1:K" & lr).Value = Range("AU" & r).Value
End If
Next r
End Sub