Budget & Expense Tracker with Variance

' Budget & Expense Tracker with Variance Analysis
' Sheet structure: Category (A), Budget (B), Actual (C), Variance (D), % Variance (E), Status (F)

Sub CalculateVariances()
    ' Calculate variances between budget and actual expenses
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Validate headers
    If ws.Cells(1, 1).Value <> "Category" Then
        MsgBox "Please ensure headers are in row 1: Category, Budget, Actual, Variance, % Variance, Status", vbExclamation
        Exit Sub
    End If

    Dim i As Long
    For i = 2 To lastRow
        Dim budget As Double
        Dim actual As Double
        Dim variance As Double
        Dim percentVariance As Double

        ' Get budget and actual values
        budget = Val(ws.Cells(i, 2).Value)
        actual = Val(ws.Cells(i, 3).Value)

        ' Calculate variance (positive = under budget, negative = over budget)
        variance = budget - actual
        ws.Cells(i, 4).Value = variance

        ' Calculate percentage variance
        If budget > 0 Then
            percentVariance = (variance / budget) * 100
            ws.Cells(i, 5).Value = percentVariance
            ws.Cells(i, 5).NumberFormat = "0.00%"
        Else
            ws.Cells(i, 5).Value = "N/A"
        End If

        ' Determine status
        Dim status As String
        If actual = 0 Then
            status = "No Data"
        ElseIf variance > 0 Then
            status = "Under Budget"
        ElseIf variance < 0 Then
            status = "Over Budget"
        Else
            status = "On Budget"
        End If

        ws.Cells(i, 6).Value = status

        ' Apply conditional formatting
        Select Case status
            Case "Under Budget"
                ws.Cells(i, 6).Interior.Color = RGB(144, 238, 144) ' Light green
            Case "Over Budget"
                ws.Cells(i, 6).Interior.Color = RGB(255, 160, 122) ' Light red
            Case "On Budget"

' ... 156 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io