The code below is supposed to be one VBA event that is activated when 'If Not Intersect(Target, Me.Range("E6:E305")) Is Nothing'
Can the code be written so that it is more efficient and less prone to errors.



' CHANGES TO COLUMN E THAT CHANGE OR DO NOT CHANGE THE BUDGET
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("E6:E305")) Is Nothing Then
'SET TRIGGER TO TRUE SO THAT IT BLOCKS SELECTION EVENT ON COLUMN E

' COST REMOVED DUE TO CANCELLATION - BUDGET NOT CHANGED
Application.EnableEvents = False
preventTrigger = True
    If Target.Value = "0" Or Target.Value = "" And Target.Offset(0, -3).Value = "Cancellation" Then
    Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = ""
    Target.Offset(0, 4).Value = Date
    Me.Protect Password:="edit"
    MsgBox "Cost removed due to Cancellation - Budget Not Changed." & vbCrLf & "" & vbCrLf & _
    "INCLUDE CANCELLATION NOTES BY DOUBLE CLICKING ON COLUMN J.", vbInformation, "CANCELLATION"
    Application.EnableEvents = True
preventTrigger = False
    Exit Sub
    End If

'NUMBER VALUES ONLY - BUDGET NOT CHANGED
Application.EnableEvents = False
preventTrigger = True
    ' Verify that the entered value is numeric
    If Not IsNumeric(Target.Value) Then
        Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
        MsgBox "Number values only - Budget Not Changed.", vbInformation, "BUDGET"
        Application.EnableEvents = True
preventTrigger = False
        Exit Sub
    End If
    
'VALUE IS NIL - BUDGET NOT CHANGED
Application.EnableEvents = False
preventTrigger = True
    If Target.Value = 0# Or Target.Value = 0 Then
    Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
    MsgBox "Value is NIL - Budget Not Changed.", vbInformation, "BUDGET"
    Application.EnableEvents = True
preventTrigger = False
    Exit Sub
    End If
    
'BUDGET PREVIOUSLY ADJUSTED - BUDGET NOT CHANGED
Application.EnableEvents = False
preventTrigger = True
    If Not Target.Comment Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
    MsgBox "Budget previously adjusted - Budget Not Changed.", vbInformation, "BUDGET"
    Application.EnableEvents = True
preventTrigger = False
    Exit Sub
    End If
    
' NO COMPLETION DATE - BUDGET NOT ADJUSTED
Application.EnableEvents = False
preventTrigger = True
    If Target.Value <> "" And Target.Offset(0, 4).Value = "" Then
    Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
    MsgBox "No Completion Date - Budget Not Changed.", vbInformation, "DATE"
    Application.EnableEvents = True
preventTrigger = False
    Exit Sub
    End If
    
'SUB SECTOR NOT APPLICABLE BUDGET NOT CHANGED
Application.EnableEvents = False
preventTrigger = True
If Target.Offset(0, -1).Value = "NA" Then
    Application.EnableEvents = False
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
    MsgBox "Sub Sector Not Applicable - Budget Not Changed.", vbInformation, "SUB SECTOR"
    Application.EnableEvents = True
    preventTrigger = False
    Exit Sub
    
ElseIf Application.WorksheetFunction.CountIf(Range("Q5:Q15"), Target.Offset(0, -1).Value) = 0 Then
    Me.Unprotect Password:="edit"
    Target.Value = Target.Offset(0, 11).Value
    Me.Protect Password:="edit"
    MsgBox "Wrong Sub Sector, Not-Present, or Miss-Spelt - Budget Not changed", vbInformation, "SUB SECTOR"
    Application.EnableEvents = True
    preventTrigger = False
    Exit Sub
End If

' VALUE CONVERTED TO NEGATIVE - BUDGET CHANGED
preventTrigger = True
    Dim confirmation As Integer
    Dim confirmationMessage As String
If IsNumeric(Target.Value) Then
    confirmation = MsgBox(confirmationMessage & vbCrLf & "A NEGATIVE value will decrease the budget." & vbCrLf & vbCrLf & _
    "A POSITIVE value will increase the budget." & vbCrLf & vbCrLf & _
    "Click YES to accept the new value." & vbCrLf & vbCrLf & _
    "Click NO to revert to the previous value.", vbYesNo + vbQuestion, "Confirm Budget Change")
    ' Check user's response
    If confirmation = vbYes Then
    Target.AddComment "Budget Value Changed " & Date
        ' Proceed with deduction or addition logic
    Dim wsActive As Worksheet
    Dim wsSectorBudget As Worksheet
    Dim sectorValue As String
    Dim subsectorRange As Range
    Dim subsectorCell As Range
    Dim costRange As Range
    Dim costCell As Range
    Dim sectorRow As Long
    
    ' Set references to the active sheet and 'Sector Budget' sheet
    Set wsActive = Me
    Set wsSectorBudget = ThisWorkbook.Sheets("Sector Budget")
    
    ' Check if the change was made in column E (range E6:E305)
    If Not Intersect(Target, wsActive.Range("E6:E305")) Is Nothing Then
    ThisWorkbook.Worksheets("Sector Budget").Unprotect Password:="edit"
        ' Get the sector value from cell D1
        sectorValue = wsActive.Range("D1").Value
        ' Find the row of the sector value in 'Sector Budget' sheet
        sectorRow = Application.Match(sectorValue, wsSectorBudget.Range("A2:A22"), 0)
        ' Check if the sector value exists in 'Sector Budget' sheet
        If Not IsError(sectorRow) Then
            ' Add 1 to the sectorRow to account for the fact that the search range starts from row 2
            sectorRow = sectorRow + 1
            ' Set the range of the subsector columns in 'Sector Budget' sheet
            Set subsectorRange = wsSectorBudget.Range("C2:C22,E2:E22,G2:G22,I2:I22,K2:K22,M2:M22,O2:O22,Q2:Q22,S2:S22,U2:U22,W2:W22")
            ' Loop through each cell in the subsector range
            For Each subsectorCell In subsectorRange.cells
                ' Check if the value matches any subsector value in active sheet
                If subsectorCell.Value = Target.Offset(0, -1).Value Then
                    ' Get the corresponding cost cell in 'Sector Budget' sheet
                    Set costCell = wsSectorBudget.cells(sectorRow, subsectorCell.Column + 1)
                    ' Add the target cost value to the existing cost value
                    costCell.Value = costCell.Value + Target.Value
                End If
            Next subsectorCell
        End If
    End If
    wsSectorBudget.Range("B2:B23").Calculate
    wsSectorBudget.Range("Z2:Z23").Calculate

        MsgBox "CHANGE RECORDED TO BUDGET.", vbInformation, "BUDGET"
       
Dim wsHistory As Worksheet
Dim lastRow As Long
Dim targetRow As Long
Set wsHistory = ThisWorkbook.Worksheets("Budget History")
ThisWorkbook.Worksheets("Budget History").Unprotect Password:="edit"
lastRow = wsHistory.cells(Rows.count, "B").End(xlUp).row
wsActive.Range("B" & Target.row & ":J" & Target.row).Copy
wsHistory.cells(lastRow + 1, "B").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Budget History").Protect Password:="edit"
ThisWorkbook.Worksheets("Sector Budget").Protect Password:="edit"

        Else
        ' Restore original value explicitly
        Application.EnableEvents = False
        Application.Undo
        Target.Value = Target.Value2
        Application.EnableEvents = True
        MsgBox "NO CHANGE TO BUDGET", vbInformation, "BUDGET"
    End If
    Me.Range("K2").Calculate
    Application.EnableEvents = True
End If
End If
preventTrigger = False