Improving Cell Change Macro

[mikeoly]

Hi Everyone!

I have some code that does what I want, but if you change the entire 'Target' range at once, it takes a while to cycle through. Any thoughts on how to circumvent the 'For' loop, or optimize in another way?

Thank you in advance!

Private Sub worksheet_change(ByVal Target As Range)

    For i = 26 To 40
            If Not Intersect(Target, Me.Range("B" & i)) Is Nothing Then
            
                Worksheets("Primary").Range("$c$" & i & ":$f$" & i).Formula = "=IF(B" & i & "="""","""",VLOOKUP(B" & i & ",Class,2,FALSE))"
        
            End If
    Next

End Sub

[Leith Ross]

Hello mikeoly,

This should run faster.

Private Sub worksheet_change(ByVal Target As Range)


    Dim i As Long
    
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
    
        If Not Intersect(Target, Range("B26:B40")) Is Nothing Then
            For i = 26 To 40
                Worksheets("Primary").Cells(i, "C").Resize(1, 4).Formula = "=IF(B" & i & "="""","""",VLOOKUP(B" & i & ",Class,2,FALSE))"
            Next i
        End If
        
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic


End Sub

[mikeoly]

Thanks, Leith!!