Exam Prep - final

Sub Main()
    Dim lastRow As Integer
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row '99

    InsertColumn
    SetExpenses (lastRow) '99
    SetProfit (lastRow) '99
    SetFullName (lastRow) '99
    SetDeliveryDate (lastRow) '99

    Range("G" & lastRow + 1) = "=SUM(G2:G" & lastRow & ")" '99
    Range("H" & lastRow + 1) = "=SUM(H2:H" & lastRow & ")" '99
    Range("I" & lastRow + 1) = "=SUM(I2:I" & lastRow & ")" '99

    DeleteColumn '99

    'lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Formatting (lastRow)
End Sub

Sub SetDeliveryDate(lastRow As Integer)
    For i = 2 To lastRow
        Range("F" & i) = "=E" & i & "+7"
    Next
End Sub

Sub SetFullName(lastRow As Integer)

    For i = 2 To lastRow
        Dim fullName, firstName, lastName As String
        firstName = Range("A" & i)
        lastName = Range("B" & i)

        fullName = firstName & " " & lastName

        Range("C" & i) = fullName
    Next
End Sub

Sub SetExpenses(lastRow As Integer)
    For i = 2 To lastRow
        Dim contractPrice, expense As Variant
        contractPrice = Range("G" & i)

        If contractPrice < 10000 Then
            expense = contractPrice * 0.03
        ElseIf contractPrice <= 30000 Then
            expense = contractPrice * 0.028
        ElseIf contractPrice <= 100000 Then
            expense = contractPrice * 0.025
        Else
            expense = contractPrice * 0.02
        End If


        Range("H" & i) = expense
    Next
End Sub

Sub SetProfit(lastRow As Integer)
    For i = 2 To lastRow
        Dim profit, contractPrice, expense As Variant
        contractPrice = Range("G" & i)
        expense = Range("H" & i)

        'profit = contractPrice - expense

        'Range("I" & i) = profit

        Range("I" & i) = "=G" & i & "-H" & i
    Next
End Sub

Sub InsertColumn()
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Expected Delivery Date"
    Range("E2").Select

    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Full Name"
    Range("C2").Select
End Sub

Sub DeleteColumn()
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

Sub Formatting(lastRow As Integer)
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .AddIndent = False
        .IndentLevel = 0
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Clients And Contracts"
    Range("A1:G1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
    End With
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Calibri"
        .Size = 16
        .ThemeColor = xlThemeColorDark1
        .ThemeFont = xlThemeFontMinor
    End With

    Range("A2:G2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
    End With



    ActiveWindow.SmallScroll Down:=96
    Range("A1:G" & lastRow).Select
    ActiveWindow.SmallScroll Down:=-126
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    'ActiveWindow.SmallScroll Down:=298


    Range("A" & lastRow + 2 & ":D" & lastRow + 2).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "TOTAL"
    Range("A" & lastRow + 2 & ":D" & lastRow + 2).Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("E" & lastRow + 2 & ":G" & lastRow + 2).Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    Range("A" & lastRow + 2 & ":D" & lastRow + 2).Select
    Selection.Font.Bold = True
    Range("A" & lastRow + 2 & ":G" & lastRow + 2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub