Best Approach for this Worksheet_Change

[Citanaf]

Hello,

I have two ranges: N11:N510 and S11:S510 that can change (vlookups) based on a data validation list in Cell E8. Regardless of the value in E8, I only want the worksheet change to trigger if a value in N11:N510 or S11:S510 changed.

If the change macro determines that a change has occurred in N11:N510, I will have a UDF input in that same row in column AQ. We'll call it UDF()
If the change macro determines that a change has occurred in S11:S510, UDF() will be input in that same row in column AU.

I realize a worksheet_change event is triggered only by a user change and not a formula, so perhaps the worksheet_calculate event is more appropriate, but I haven't used it before and am unsure of how to approach it.

Bonus points if you add a line to copy/paste the value to remove the UDF().

Let me know if you need further clarity.

Thanks

 

[Kamolga]

My approach to activate a macro on value change (works with formula):

For example C1=A1+B1 and macro would therefore run if A1 or B1 change with C1 in target

[TABLE="width: 588"]
<tbody>[TR]
[TD="class: xl63, width: 588"]Private  Sub Worksheet_Calculate()
    Static oldval
    If Range("C1").Value <> oldval Then
        oldval =  Range("C1").Value
        
        'rest of code here
        
        
    End If
    End Sub[/TD]
[/TR]
</tbody>[/TABLE]

I guess that If you start by

Dim TheCell as range
For each TheCell in Range("N11:N510")

Code

Next TheCell

You could get what you want