Consulting

Results 1 to 3 of 3

Thread: Range not selecting correctly?

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location

    Range not selecting correctly?

    Hello again everyone,

    I am working on a project that is almost complete but I am having an issue that I cannot figure out. I have one block of code that I run and at the end of that code it runs another block of code. Both work fine separately but for some reason when I run them together the second block does not select the correct range. It does select the correct range if I run that part separately though. Here are the two blocks

    #1

    Option Explicit
    Sub Load_Add_New()
    '
    ' New_Load Macro
    '
    
    
    'Message Box if Nothing is on the Line
        If Range("C14") = 0 Then
                Dim Msg, Style, Title, Response
                    Msg = "You have not entered a transaction on this line."
                        Style = vbOKOnly + vbExclamation
                        
                        Title = "CANNOT ADD A LINE"
                        Response = MsgBox(["You have not entered a transaction on this line."], vbOKOnly + vbExclamation, ["CANNOT ADD A LINE"])
            Exit Sub
        End If
    
    
    'Create New Row
        'Application.ScreenUpdating = False
        Sheets("Loads Completed").Select
        ActiveSheet.unprotect
            Range("C13:AR1000").Sort Key1:=Range("F13"), Order1:=xlDescending, _
                                    Key2:=Range("D13"), Order2:=xlAscending, Header:=xlNo
    
    
        Rows("14:14").Select
        Rows("14:14").Insert Shift:=xlDown
        
    'Set Formatting For New Row
        Selection.RowHeight = 15
        With Selection.Font
            .Name = "Calibri"
            .Size = 11
        End With
        With Selection.Font
            .ColorIndex = xlAutomatic
            .Bold = False
        End With
        
    'Set Formatting & Formula For Line Number
        Range("A14").Select
        Selection.NumberFormat = "0"
        Selection.Locked = True
        With Selection
            .VerticalAlignment = xlCenter
            .WrapText = False
            .MergeCells = False
        End With
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        ActiveCell.FormulaR1C1 = "=R[1]C1+1"
        ActiveCell.Offset(0, 2).Select
            ActiveCell.FormulaR1C1 = "1"
    
    
    
    
    Dim LR As Long
    LR = Range("C14:AI14").End(xlDown).Row
    
    
    Dim R1 As Range, R2 As Range, MultiRange As Range
    Set R1 = Range("C14:AI" & LR)
    Set R2 = Range("AK14:AQ" & LR)
    Set MultiRange = Union(R1, R2)
    
    
    
    
    
    
    MultiRange.Select
    Range("C14").Activate
    
    
        Cells.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD($A14,2)=1"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Pattern = xlNone
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
        Selection.FormatConditions(1).StopIfTrue = True
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD($A14,2)=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Pattern = xlNone
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = True
        
    'Select B14 to set formatting and Formula
        Range("C14").Select
            ActiveCell.ClearContents
        Range("B14").Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(RC3<>"""",IF(LEFT(RC3,2)=""DH"",CONCATENATE(RC3,"" "",""Deadhead Miles""),CONCATENATE(RC3,"" "",""Trip Miles"")),""""),"""")"
        
    'Select Load # Field to set formatting
        Range("C14").Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlMedium
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select Pick Up Date Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "m-d-yyyy"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select Delivery Date to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "m-d-yyyy"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
       
    'Select Settlement Date to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "m-d-yyyy"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(RC3<>"""",VLOOKUP(RC2,'Settlement Account'!R12C2:R2500C8,7,0),""""),"""")"
       
    'Select "# of Stops" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Detention Hours" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.00"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
    
    
    'Select "P/U Customer" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "P/U Location" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Final Customer" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Final Location" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Weight" Field to set formmating
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "#,##0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Loaded Miles" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "#,##0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Empty Miles" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "#,##0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Total Miles" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "#,##0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(OR(RC14<>"""",RC15<>""""),SUM(RC14,RC15),"""")"
        
    'Select "Actual Miles" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "#,##0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Difference" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0%"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC16<>"""",RC17<>""""),1-RC16/RC17,"""")"
        
    'Select "Loaded Rate" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC3<>"""",RC14<>""""),IF(AND(RC14<800,RC3<>""""),VLOOKUP(RC14,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0),VLOOKUP(800,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0)),"""")"
        
    'Select "Empty Rate" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC3<>"""",RC15<>""""),VLOOKUP(RC15,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0),"""")"
        
    'Select "Gallons Used" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Idle Gallons" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Driving Gallons" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(RC21<>"""",RC21-RC22,"""")"
        
    'Select "Driving MPG" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC17<>"""",RC23<>""""),RC17/RC23,"""")"
        
    'Select "Overall MPG" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "0.0"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC17<>"""",RC21<>""""),RC17/RC21,"""")"
        
    'Select "Cost Per Gallon" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        
    'Select "Fuel Cost Driving" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC23<>"""",RC26<>""""),RC23*RC26,"""")"
        
    'Select "Fuel Cost Idling" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC22<>"""",RC26<>""""),RC22*RC26,"""")"
        
    'Select "Total Fuel Cost" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(RC27<>"""",SUM(RC27,RC28),"""")"
    
    
    'Select "Loaded Revenue" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC14<>""""),RC14*RC19,"""")"
    
    
    'Select "Empty Revenue" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC15<>""""),RC15*RC20,"""")"
        
    'Select "Extra Stop Revenue" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC7<>""""),RC7*35,"""")"
        
    'Select "Detention Revenue" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC8<>""""),RC8*30,"""")"
        
    'Select "Total Revenue" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC14<>"""",RC19<>""""),SUM(RC30:RC33),"""")"
        
    'Select "Net" Field to set formatting
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlMedium
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC29<>"""",RC34<>""""),RC34-RC29,"""")"
        
    'Selects "Amount Paid" to set formatting and formula
        ActiveCell.Offset(0, 2).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlMedium
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=SUM(RC46:RC49)"
        
    'Selects "Difference" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC34<>"""",RC37<>""""),RC37-RC34,"""")"
    
    
    'Selects "Cost By Run" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(AND(RC3<>"""",RC16<>""""),SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C8:R5000C8,RC6,'Settlement Account'!R12C3:R5000C3,""Settlement"",'Settlement Account'!R12C12:R5000C12,""Fixed Cost"")*(RC16/SUMIF(R13C6:R191C6,RC6,R13C16:R191C16)),0),0)"
            
    'Selects "CPM By Run" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC39<>""""),(RC37-RC39)/RC16,0)"
            
    'Selects "Additional Revenue" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(RC3<>"""",SUM(RC50:RC52),0)"
            
    'Selects "Total CPM by Run" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        ActiveCell.FormulaR1C1 = "=IF(RC3<>"""",(SUM(RC37,RC41)-RC39)/RC16,0)"
            
    'Selects "" to set formatting and formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "General"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlCenter
                .WrapText = False
                .MergeCells = False
                .Locked = False
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlMedium
            End With
            
    'Selects "Deadhead Revenue" to set formula
        ActiveCell.Offset(0, 3).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF((LEFT(RC3,2)=""DH""),IFERROR(VLOOKUP(CONCATENATE(RC3,"" "",""Deadhead Miles""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"
    
    
    'Selects "Trip Revenue" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(AND(RC3<>"""",RC14<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Trip Miles""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"
    
    
    'Selects "Extra Stop Revenue" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(AND(RC3<>"""",RC7<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Extra Stop Pay""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"
    
    
    'Selects "Detention Revenue" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(IF(AND(RC3<>"""",RC8<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Detention Pay""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"
        
    'Selects "Additional Revenue" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF(RC[-47]<>"""",SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C11:R5000C11,""Revenue"",'Settlement Account'!R12C8:R5000C8,R14C6:R190C6,'Settlement Account'!R12C5:R5000C5,R14C3:R190C3)-RC37,0)"
        
    'Selects "Draws Allocated by Mile" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC3<>"""",RC16=0),SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C12:R5000C12,""Draw"",'Settlement Account'!R12C8:R5000C8,RC6),IF(AND(RC3<>"""",RC16<>0),SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C12:R5000C12,""Draw"",'Settlement Account'!R12C8:R5000C8,RC6)*(RC16/SUMIF(R14C6:R190C6,RC6,R14C16:R190C16)),0))"
    
    
    'Selects "Previous Settlement Balance" to set formula
        ActiveCell.Offset(0, 1).Select
            With Selection
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlGeneral
                .WrapText = False
                .MergeCells = False
                .Locked = True
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
            End With
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC3<>0,RC16=0),0-SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C10:R5000C10,""Settle Previous Negative Balance"",'Settlement Account'!R12C8:R5000C8,RC6),IF(AND(RC[-49]<>0,RC[-36]<>0),0-SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C10:R5000C10,""Settle Previous Negative Balance"",'Settlement Account'!R12C8:R5000C8,RC6)*RC16/(SUMIF(R14C6:R190C6,RC6,R14C16:R190C16)),0))"
        
        Sheets("Loads Completed").Select
        Application.ScreenUpdating = True
        Range("C14").Select
        Application.ScreenUpdating = False
        ActiveSheet.Protect
            Range("C14").Select
            Application.CutCopyMode = False
            Application.Run "'Owner Operater Run Log.xlsm'!Load_Analysis"
            
    End Sub
    #2

    Option Explicit
    Sub Load_Analysis()
    '
    ' Macro1 Macro
    '
    
    
    '
        
            Dim lastRow As Long
            lastRow = Range("$B$7:$Y$7").End(xlDown).Row
            
            Dim rangeTwo As Range
                
                Sheets("Load Analysis").Select
                ActiveSheet.unprotect
                'Application.ScreenUpdating = False
                Range("A7").Select
        
        Do
            Range("A7").Select
            If ActiveCell.Offset(0, 0).Range("A1") <> Sheets("Loads Completed").Range("A14") Then
                Rows("7:7").Select
                Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
                Range("B7:Y7").Select
                With Selection.Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                With Selection.Font
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                End With
                Selection.Font.Bold = False
                Rows("7:7").RowHeight = 15
            
            'Selects "Number Column" to set format and formula
                Range("A7").Select
                        With Selection
                            .NumberFormat = "0"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlCenter
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlNone
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlMedium
                        End With
                    ActiveCell.FormulaR1C1 = "=R[1]C1+1"
                Range("B7").Select
                End If
        Loop While ActiveCell.Offset(0, 0) <> Sheets("Loads Completed").Range("A14")
                
                
            'Selects "Total Miles" to set format and formula
                ActiveCell.Offset(0, 2).Select
                        With Selection
                            .NumberFormat = "#,##0"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlCenter
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",VLOOKUP(RC2,'Loads Completed'!R14C3:R500C44,MATCH(R6C,'Loads Completed'!R13C3:R13C44,0),0),""""),"""")"
                
            'Selects "Percentage of Miles" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "0.00%"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlCenter
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC3/SUMIF(R7C5:R500C5,RC5,R7C3:R500C3),""""),"""")"
                
            'Selects "Settlement Date" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "M-D-YYYY"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlCenter
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",VLOOKUP(RC2,'Loads Completed'!R14C3:R500C44,MATCH(R6C,'Loads Completed'!R13C3:R13C44,0),0),""""),"""")"
                
            'Selects "Revenue Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUM(SUMIFS('Settlement Account'!R13C7:R5000C7,'Settlement Account'!R13C5:R5000C5,RC2,'Settlement Account'!R13C11:R5000C11,""Revenue""),SUMIFS('Settlement Account'!R13C7:R5000C7,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,""Revenue Draw"")*(RC3/SUMIF(R7C5:R500C5,RC5,R7C3:R500C3))),""""),"""")"
                
            'Selects "Revenue CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC6/RC3,""""),"""")"
                
            'Selects "Truck Payment Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Truck Payment CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC8/RC3,""""),"""")"
                
            'Selects "Diesel Fuel Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Diesel Fuel CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC10/RC3,""""),"""")"
                
            'Selects "Computer Rental Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Computer Rental CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC12/RC3,""""),"""")"
                
            'Selects "Insurance Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Insurance CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC12/RC3,""""),"""")"
                
            'Selects "Maintenance Account Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Maintenance Account CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC16/RC3,""""),"""")"
                
            'Selects "Tolls Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Tolls CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC18/RC3,""""),"""")"
                
            'Selects "Taxes Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"
                
            'Selects "Taxes CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC20/RC3,""""),"""")"
                
            'Selects "Total Cost Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUM(RC8,RC10,RC12,RC14,RC16,RC18,RC20),""""),"""")"
                
            'Selects "Total Cost CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = _
                        "=IFERROR(IF(RC2<>"""",SUM(RC9,RC11,RC13,RC15,RC17,RC19,RC21),""""),"""")"
                
            'Selects "Net Profit Amount" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlThin
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC6-RC22,""""),"""")"
                
            'Selects "Net Profit CPM" to set format and formula
                ActiveCell.Offset(0, 1).Select
                        With Selection
                            .NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
                            .VerticalAlignment = xlCenter
                            .HorizontalAlignment = xlGeneral
                            .WrapText = False
                            .MergeCells = False
                            .Locked = True
                            .Borders(xlEdgeTop).LineStyle = xlNone
                            .Borders(xlEdgeBottom).LineStyle = xlNone
                            .Borders(xlEdgeLeft).LineStyle = xlContinuous
                            .Borders(xlEdgeLeft).Weight = xlThin
                            .Borders(xlEdgeRight).LineStyle = xlContinuous
                            .Borders(xlEdgeRight).Weight = xlMedium
                        End With
                    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC24/RC3,""""),"""")"
                Range("H7").Select
                Range("A7").Select
                    If ActiveCell.Offset(1, 1) = "" Then
                    Range("C7:Y7").Select
                        Selection.Copy
                    Do
                        If ActiveCell.Offset(0, -2) <> 1 Then
                            ActiveCell.Offset(1, 0).Select
                                Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
                                    False, Transpose:=False
                        End If
                    Loop Until ActiveCell.Offset(0, -2) = 1
                    End If
                    Sheets("Loads Completed").Select
                    Range("I14").Select
                    Range("C15").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    Sheets("Load Analysis").Select
                    Range("B8").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Range("B7").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF('Loads Completed'!R[7]C3<>"""",'Loads Completed'!R[7]C3,"""")"
                    
            Set rangeTwo = Range("$B$7:$Y$" & lastRow)
                rangeTwo.Select
                
                    Cells.FormatConditions.Delete
                    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                        "=MOD($A7,2)=1"
                    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    With Selection.FormatConditions(1)
                        .Font.ColorIndex = xlAutomatic
                        .Font.TintAndShade = 0
                        .Borders(xlTop).LineStyle = xlNone
                        .Borders(xlBottom).LineStyle = xlNone
                        .Interior.Pattern = xlNone
                        .Interior.TintAndShade = 0
                    End With
                    Selection.FormatConditions(1).StopIfTrue = True
                    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                        "=MOD($A7,2)=0"
                    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    With Selection.FormatConditions(1)
                        .Font.ColorIndex = xlAutomatic
                        .Font.TintAndShade = 0
                        .Borders(xlTop).LineStyle = xlNone
                        .Borders(xlBottom).LineStyle = xlNone
                        .Interior.PatternColorIndex = xlAutomatic
                        .Interior.ThemeColor = xlThemeColorDark1
                        .Interior.TintAndShade = -0.249946592608417
                    End With
                    Selection.FormatConditions(1).StopIfTrue = True
                    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                        "=AND($A6<>"""",$E7<>$E6)"
                    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    With Selection.FormatConditions(1)
                        .Borders(xlTop).LineStyle = xlContinuous
                        .Borders(xlTop).Weight = xlThin
                        .Borders(xlTop).Color = -16776961
                        .Borders(xlTop).TintAndShade = 0
                    End With
                    Selection.FormatConditions(1).StopIfTrue = False
                    Range("B7").Select
                    ActiveSheet.Protect
        
    
    
    
    
    End Sub
    When the code runs through the "Sub Load Analysis" as part of the "Sub Load_Add_New" when it selects the range declared variable range "rangeTwo" it only selects the first 10 lines and this sheet currently has 17 lines on it, it should be selecting all 17. When I run this code by itself it selects the range correctly. I am thinking that something is getting confused between the two blocks of code but I cannot find it for the life of me. Can anyone help? Thank you in advance.
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    Why don't you clean up those Macros and turn them into real Code first?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    The last line of the first macro is:
    [VBa] Application.Run "'Owner Operater Run Log.xlsm'!Load_Analysis"
    [/VBA]

    which implies the second macro is in a different workbook,

    At the start of the second macro you have:

    [vba]Sub Load_Analysis()
    '
    ' Macro1 Macro
    '


    '

    Dim lastRow As Long
    lastRow = Range("$B$7:$Y$7").End(xlDown).Row

    Dim rangeTwo As Range

    Sheets("Load Analysis").Select
    [/vba]

    So the last row calculation does it's calculation on whatever workbook/worksheet the last macro left it on. I suspect your error is because you are not correctly selecting the workbook and worksheet at the start of the second macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •