Running macro individually instead of entire sheet

[Elysium4Eternity]

Sub Financiël_Gevel_€_M²_JAAR()


Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long


For i = 3 To 4001 Step 4
    
If Range("F" & i).Value = "Elektriciteit" Then
For j = 3 To 4001 Step 4
Range("L" & i).FormulaR1C1 = "=r[-1]c*r5c34"
Next j


ElseIf Range("F" & i).Value = "Aardgas" Then


For k = 3 To 4001 Step 4
Range("L" & i).FormulaR1C1 = "=r[-1]c*r7c34"
        
Else
        
For l = 3 To 4001 Step 4
Range("L" & i).FormulaR1C1 = "=r[-1]c*r9c34"
Next l
    
End If


Next i


End Sub

In my sheet F3 is a cell in wich you can choose one of the following options: Elektriciteit, Aaardgas and Stookolie. But I want my code to run automatically when I switch between one of those options. I know how to do this but the problem is that the method I know of will run the macro for the entire sheet. And since I have 4000 lines it will take a long time for excel to process this. Is there a way to run the macro individually for a few rows instead of the entire sheet?

 

[RoryA]

I don't really understand the purpose of the j, k and l loops since you are only writing the same formula to the same cell repeatedly. I'm not even sure you really need code as you could use an IF formula in the cells directly.

In any event, you could use a Change event in the Worksheet, something like this:

private sub worksheet_change(byval target as range)
dim cell as range, lRow as long
if not intersect(Target, Range("F:F")) is nothing then
   on error goto clean_up
   application.enableevents = false
   for each cell in intersect(Target, Range("F:F")).Cells
      select case cell.value
         case "Elektriciteit"
            lrow = 5
        Case "Aardgas"
           lRow = 7
        case else
           lRow = 9
      end select
      cells(cell.Row, "L").FormulaR1C1 = "=r[-1]c*r" & lRow & "c34"
   next cell
  
end if

clean_up:
   application.enableevents = true
end sub

Note: that code must be in the worksheet code module - right-click the sheet tab and choose 'View Code' from the menu, then paste it in.