In my worksheet, column L value is getting updated through a formula. Whenever L column data is changing, I need to run a function which will update M and K columns. I am using the following code to do that:
Private Sub Worksheet_Calculate()
Dim Ticker As Worksheet
Dim target As Range, val As Long, LastRaw As Long, i As Long
Set Ticker = Workbooks("FyersOne.xlsm").Sheets("Ticker")
LastRaw = Range("A" & Rows.Count).End(xlUp).Row
Set target = Ticker.Range("L2:L" & LastRaw)
If Not Intersect(target, Range("L2")) Is Nothing Then
For i = 2 To LastRaw
Ticker.Cells(i, "M").Value = Ticker.Cells(i, "J").Value - Ticker.Cells(i, "K").Value
Ticker.Cells(i, "K").Value = Ticker.Cells(i, "J").Value
Next i
End If
End Sub
I need to run the subroutine only on those rows whose L column data is updating. But using above code, the procedure is calculating for all the rows.
In my use case the L column data will update at different times for each row, so I need run the code only for those rows.
If I use the Worksheet_Change handler I can check the rows which are updating in L column. But the problem is Worksheet_Change will not trigger because the value is changing through cell formulas.
Also, Excel is becoming slow due to the code running through all the rows each time. Is there any way I can perform this procedure only on those rows where L column value is getting updated?
