Attribute VB_Name = "Module1" Sub FW_Cas_Only() ' ' FW_Cas_Only Macro ' ' Macro reformats annular production cells and shoe test pressure cell if well has freshwater casing only ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 4).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Y" Cells(a, 23).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "S/C" Range(Cells(a, 13).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Multi_String() ' ' Mult_String Macro ' ' Macro reformats annular production cells and makes shoe test pressure cell available if well has more than freshwater casing only ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 4).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 4).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "N" Cells(a, 23).Offset(b, 0).Select Selection.ClearContents Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 13).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Ann_Prod_NFW() ' ' Ann_Prod_NFW Macro ' ' Macro reformats surface or coal casing set depth cell and makes shoe test pressure cell avaialbe if well is not producing annular gas inside FW string ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 6).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "N" Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 65).Offset(b, 0).Select Selection.ClearContents Else: End If Next a End Sub Sub Ann_Prod_FW() ' ' Ann_Prod_FW Macro ' ' Macro reformats surface or coal casing set depth cell and hashes out shoe test pressure cell if well is producing annular gas inside FW string ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 6).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Y" Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 65).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Y" Else: End If Next a End Sub Sub Ann_Prod_N() ' ' Ann_Prod_N Macro ' ' Macro reformats surface or coal casing annular production cell and deletes "P" annulus if well is not producing annular gas ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 6).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "N" Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 12).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Range(Cells(a, 13).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 19).Offset(b, 0), Cells(a, 20).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "P" Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Cells(a, 23).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Ann_Prod_Y() ' ' Ann_Prod_Y Macro ' ' Macro reformats surface or coal casing annular production cell and records "P" annulus if well is producing annular gas ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 6).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Y" Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "P" Range(Cells(a, 13).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 19).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(b, 0).Select Selection.ClearContents Cells(a, 15).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "P" Cells(a, 23).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If If Cells(a, 2).Offset(b, 0) = "Combo" Then Range(Cells(a, 12).Offset(b, 0), Cells(a, 12).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Oil() ' ' Oil Macro ' ' Macro reformats fluid level, freshwater casing, annular production, wellhead pressure, surface casing set depth, and shoe test pressure cells ' MUST still add updates on hidden tally sheet for master database: record "OIL" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select Selection.ClearContents Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 6).Offset(b, 0).Select Selection.ClearContents Cells(a, 2).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Oil" Cells(a, 5).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 6).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Range(Cells(a, 4).Offset(b, 0), Cells(a, 4).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 3).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(b, 0), Cells(a, 18).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 19).Offset(b, 0), Cells(a, 20).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Cells(a, 23).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "P" Else: End If Next a End Sub Sub Gas() ' ' Gas Macro ' ' Macro reformats fluid level, freshwater casing, annular production, wellhead pressure, surface casing set depth, and shoe test pressure cells ' MUST still add updates on hidden tally sheet for master database: record "GAS" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select Selection.ClearContents Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 6).Offset(b, 0).Select Selection.ClearContents Cells(a, 2).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Gas" Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 3).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(b, 0), Cells(a, 18).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Cells(a, 23).Offset(b, 0).Select Selection.ClearContents Else: End If Next a End Sub Sub Combo() ' ' Combo ' ' Macro reformats fluid level, produced water quality, freshwater casing, annular production, wellhead pressure, surface casing set depth, and shoe test pressure cells ' MUST still add updates on hidden tally sheet for master database: record "COMBO" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select Selection.ClearContents Cells(a, 6).Offset(b, 0).Select Selection.ClearContents Cells(a, 2).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Combo" Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 4).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 3).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(b, 0), Cells(a, 18).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Else: End If Next a End Sub Sub Oil_FW() ' ' Oil_FW Macro ' ' Macro reformats annular production, wellhead pressure, surface casing set depth, and shoe test pressure cells ' MUST still add updates on hidden tally sheet for master database: record "OIL" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select Selection.ClearContents Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 6).Offset(b, 0).Select Selection.ClearContents Cells(a, 2).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Oil (Freshwater Casing Only)" Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 3).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(b, 0), Cells(a, 20).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Cells(a, 23).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "S/C" Else: End If Next a End Sub Sub Combo_FW() ' ' Combo_FW Macro ' ' Macro reformats annular production, wellhead pressure, surface casing set depth, and shoe test pressure cells ' MUST still add updates on hidden tally sheet for master database: record "OIL" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select Selection.ClearContents Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 6).Offset(b, 0).Select Selection.ClearContents Cells(a, 2).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Combo (Freshwater Casing Only)" Range(Cells(a, 5).Offset(b, 0), Cells(a, 6).Offset(b, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 5).Offset(b, 0).Select Selection.ClearContents Cells(a, 4).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 12).Offset(b, 0), Cells(a, 12).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 13).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 3).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(b, 0), Cells(a, 20).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(b, 0).Select Selection.ClearContents Cells(a, 23).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "S/C" Else: End If Next a End Sub Sub Fluid_Level_Y() ' ' Fluid_Level_Y Macro ' ' Macro reformats annular production cells and shoe test pressure cell if well has freshwater casing only ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "Y" Range(Cells(a, 16).Offset(b, 0), Cells(a, 16).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 17).Offset(b, 0), Cells(a, 18).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Fluid_Level_N() ' ' Fluid_Level_N Macro ' ' Macro reformats annular production cells and shoe test pressure cell if well has freshwater casing only ' MUST still add updates on hidden tally sheet for master database: if "no", write "N", if "yes", write "Y" ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 67).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 And Cells(a, 74).Offset(b, 0) = 0 Then Cells(a, 3).Offset(b, 0).Select ActiveCell.FormulaR1C1 = "N" Range(Cells(a, 16).Offset(b, 0), Cells(a, 16).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 17).Offset(b, 0), Cells(a, 18).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With If Cells(a, 2).Offset(b, 0) = "Oil (Freshwater Casing Only)" Then Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Else: End If Next a End Sub Sub Fix_Combo_FW() Attribute Fix_Combo_FW.VB_ProcData.VB_Invoke_Func = "a\n14" ' ' Fix_Combo_FW Macro ' ' Macro Turns Casing Depth Cells to Yellow ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 2).Offset(b, 0) = "Combo (Freshwater Casing Only)" Then Cells(a, 8).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Annular_Spaces() ' ' Annular_Spaces Macro ' ' Macro reformats columns based on number of annular spaces ' MAY still need to add updates on hidden tally sheet for master database ' Dim a As Integer Dim b As Integer For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 7).Offset(b, 0) = 5 And Cells(a, 70).Offset(b, 0) = 0 Then Cells(a, 24).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 33).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(b, 0), Cells(a, 35).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 37).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 38).Offset(b, 0), Cells(a, 38).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(b, 0), Cells(a, 46).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 48).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 49).Offset(b, 0), Cells(a, 49).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(b, 0), Cells(a, 57).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If If Cells(a, 7).Offset(b, 0) = 4 And Cells(a, 70).Offset(b, 0) = 0 Then Cells(a, 24).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 33).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(b, 0), Cells(a, 35).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 37).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 38).Offset(b, 0), Cells(a, 38).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(b, 0), Cells(a, 46).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 48).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 49).Offset(b, 0), Cells(a, 49).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(b, 0), Cells(a, 57).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If If Cells(a, 7).Offset(b, 0) = 3 And Cells(a, 70).Offset(b, 0) = 0 Then Cells(a, 24).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 33).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(b, 0), Cells(a, 35).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 37).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 38).Offset(b, 0), Cells(a, 38).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(b, 0), Cells(a, 46).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 48).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 49).Offset(b, 0), Cells(a, 49).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(b, 0), Cells(a, 57).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If If Cells(a, 7).Offset(b, 0) = 2 And Cells(a, 70).Offset(b, 0) = 0 Then Cells(a, 24).Offset(b, 0).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 33).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(b, 0), Cells(a, 35).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 37).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 38).Offset(b, 0), Cells(a, 38).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(b, 0), Cells(a, 46).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 48).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 49).Offset(b, 0), Cells(a, 49).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(b, 0), Cells(a, 57).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If If Cells(a, 7).Offset(b, 0) = 1 And Cells(a, 70).Offset(b, 0) = 0 Then Cells(a, 33).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(b, 0), Cells(a, 35).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 24).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(b, 0), Cells(a, 46).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(b, 0), Cells(a, 57).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(b, 0).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Same_Prev() ' ' Same_Prev Macro ' Macro transfers MIA data from previous quarter if conditions at well have not changed ' Dim a As Integer For a = 11 To 1009 Application.ScreenUpdating = False If Cells(a - 1, 68) = 1 And Cells(a, 69) = 0 And Cells(a, 11) = "Y" Then Range(Cells(a - 1, 12), Cells(a - 1, 13)).Select Selection.Copy Range(Cells(a, 12), Cells(a, 13)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 16), Cells(a - 1, 18)).Select Selection.Copy Range(Cells(a, 16), Cells(a, 18)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 19), Cells(a - 1, 21)).Select Selection.Copy Range(Cells(a, 19), Cells(a, 21)).Select Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range(Cells(a - 1, 25), Cells(a - 1, 26)).Select Selection.Copy Range(Cells(a, 25), Cells(a, 26)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 28), Cells(a - 1, 29)).Select Selection.Copy Range(Cells(a, 28), Cells(a, 29)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 31), Cells(a - 1, 32)).Select Selection.Copy Range(Cells(a, 31), Cells(a, 32)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 34), Cells(a - 1, 36)).Select Selection.Copy Range(Cells(a, 34), Cells(a, 36)).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 38).Select Selection.Copy Cells(a, 38).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 40).Select Selection.Copy Cells(a, 40).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 42).Select Selection.Copy Cells(a, 42).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 44).Select Selection.Copy Cells(a, 44).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 46), Cells(a - 1, 47)).Select Selection.Copy Range(Cells(a, 46), Cells(a, 47)).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 49).Select Selection.Copy Cells(a, 49).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 51).Select Selection.Copy Cells(a, 51).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 53).Select Selection.Copy Cells(a, 53).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(a - 1, 55).Select Selection.Copy Cells(a, 55).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 57), Cells(a - 1, 58)).Select Selection.Copy Range(Cells(a, 57), Cells(a, 58)).Select Selection.PasteSpecial Paste:=xlPasteValues Range(Cells(a - 1, 60), Cells(a - 1, 62)).Select Selection.Copy Range(Cells(a, 60), Cells(a, 62)).Select Selection.PasteSpecial Paste:=xlPasteValues Else: End If Next a End Sub Sub Create_New_Template() ' ' Create_New_Template Macro ' ' Macro creates template to store next year's data and saves previous years data under new name ' Dim a As Integer Dim b As Integer Application.ScreenUpdating = False Sheets("Quarterly_MIA_Data").Select If Cells(6, 8) = "Y" Then Sheets("Quarterly_MIA_Data").Copy Before:=Sheets(2) Sheets("Quarterly_MIA_Data").Name = "Last_Years_Data" Sheets("Quarterly_MIA_Data (2)").Select Range(Cells(10, 7), Cells(1009, 7)).Select Selection.Copy Cells(10, 75).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Quarterly_MIA_Data (2)").Name = "Quarterly_MIA_Data" Sheets("Quarterly_MIA_Data").Select Else: End If For a = 1 To 250 b = (3 * a) + 6 If Cells(a, 70).Offset(b, 0) > 0 Then Cells(6, 3).Select Selection.ClearContents Cells(7, 3).Select Selection.ClearContents Cells(6, 8).Select Selection.ClearContents Range(Cells(a, 9).Offset(b, 0), Cells(a, 9).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 11).Offset(b, 0), Cells(a, 11).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 16).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 19).Offset(b, 0), Cells(a, 20).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 34).Offset(b, 0), Cells(a, 36).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 38).Offset(b, 0), Cells(a, 38).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 40).Offset(b, 0), Cells(a, 40).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 42).Offset(b, 0), Cells(a, 42).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 44).Offset(b, 0), Cells(a, 44).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 46).Offset(b, 0), Cells(a, 47).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 49).Offset(b, 0), Cells(a, 49).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 51).Offset(b, 0), Cells(a, 51).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 53).Offset(b, 0), Cells(a, 53).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 55).Offset(b, 0), Cells(a, 55).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 57).Offset(b, 0), Cells(a, 58).Offset(b + 3, 0)).Select Selection.ClearContents Range(Cells(a, 60).Offset(b, 0), Cells(a, 63).Offset(b + 3, 0)).Select Selection.ClearContents Cells(a, 65).Offset(b, 0).Select Selection.ClearContents Else: End If Next a For a = 1 To 250 b = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 5).Offset(b, 0) = "Y" Then Range(Cells(a, 19).Offset(b, 0), Cells(a, 21).Offset(b + 3, 0)).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a Cells(1, 4).Select End Sub Sub Production_Shut_In() ' ' Production_Shut_In ' ' Macro reformats production annulus flow/pressure cells and blow-down cells ' MUST still add updates on hidden tally sheet for master database ' Dim a As Integer For a = 10 To 1009 Application.ScreenUpdating = False If Cells(a, 68) > 0 And Cells(a, 77).Offset(1, 0) = 0 And Cells(a, 69) = 0 Then Cells(a, 19).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 20).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 21).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Production_Venting() ' ' Production_Shut_In ' ' Macro reformats production annulus flow/pressure cells and blow-down cells ' MUST still add updates on hidden tally sheet for master database ' Dim a As Integer For a = 10 To 1009 Application.ScreenUpdating = False If Cells(a, 68) > 0 And Cells(a, 77).Offset(1, 0) = 0 And Cells(a, 69) = 0 Then Cells(a, 19).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 20).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 21).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Production_Inaccessible() ' ' Production_Inaccessible ' ' Macro reformats production annulus flow/pressure cells and blow-down cells ' MUST still add updates on hidden tally sheet for master database ' Dim a As Integer Dim b As Integer For a = 10 To 1009 Application.ScreenUpdating = False If Cells(a, 68) > 0 And Cells(a, 77).Offset(1, 0) = 0 And Cells(a, 69) = 0 Then Cells(a, 19).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 20).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 21).Select With Selection.Interior .Pattern = xlLightUp .PatternColorIndex = xlAutomatic .ColorIndex = xlAutomatic .TintAndShade = 0 .PatternTintAndShade = 0 End With Else: End If Next a End Sub Sub Same_4_Prev() ' ' Same_4_Prev Macro ' Macro transfers MIA data from 4th quarter of previous year to 1st quarter of current year if conditions at well have not changed ' Dim a As Integer Dim b As Integer Dim c As Integer For a = 1 To 250 b = (3 * a) + 9 c = (3 * a) + 6 Application.ScreenUpdating = False Sheets("Quarterly_MIA_Data").Select If Cells(a, 68).Offset(c, 0) = 1 And Cells(a, 69).Offset(c, 0) = 0 And Cells(a, 11).Offset(c, 0) = "Y" Then Sheets("Last_Years_Data").Select Range(Cells(a, 12).Offset(b, 0), Cells(a, 13).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 12).Offset(c, 0), Cells(a, 13).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 16).Offset(b, 0), Cells(a, 18).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 16).Offset(c, 0), Cells(a, 18).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 19).Offset(b, 0), Cells(a, 21).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 19).Offset(c, 0), Cells(a, 21).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Sheets("Last_Years_Data").Select Range(Cells(a, 25).Offset(b, 0), Cells(a, 26).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 25).Offset(c, 0), Cells(a, 26).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 28).Offset(b, 0), Cells(a, 29).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 28).Offset(c, 0), Cells(a, 29).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 31).Offset(b, 0), Cells(a, 32).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 31).Offset(c, 0), Cells(a, 32).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 34).Offset(b, 0), Cells(a, 36).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 34).Offset(c, 0), Cells(a, 36).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 38).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 38).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 40).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 40).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 42).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 42).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 44).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 44).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 46).Offset(b, 0), Cells(a, 47).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 46).Offset(c, 0), Cells(a, 47).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 49).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 49).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 51).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 51).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 53).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 53).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Cells(a, 55).Offset(b, 0).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Cells(a, 55).Offset(c, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 57).Offset(b, 0), Cells(a, 58).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 57).Offset(c, 0), Cells(a, 58).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("Last_Years_Data").Select Range(Cells(a, 60).Offset(b, 0), Cells(a, 62).Offset(b, 0)).Select Selection.Copy Sheets("Quarterly_MIA_Data").Select Range(Cells(a, 60).Offset(c, 0), Cells(a, 62).Offset(c, 0)).Select Selection.PasteSpecial Paste:=xlPasteValues Else: End If Next a End Sub Sub Reset_Section() ' ' Reset_Section Macro ' ' Macro resets section of spreadsheet designed to receive quarterly data for a single well ' Dim a As Integer Dim b As Integer Dim c As Integer For a = 1 To 250 b = (3 * a) + 9 c = (3 * a) + 6 Application.ScreenUpdating = False If Cells(a, 76).Offset(c, 0) = "Y" Then Range(Cells(a, 75).Offset(c, 0), Cells(a, 75).Offset(c + 3, 0)).Select Selection.ClearContents Cells(a, 1).Offset(b - 2, 0).Select Selection.ClearContents Cells(a, 1).Offset(b, 0).Select Selection.ClearContents Range(Cells(a, 2).Offset(c, 0), Cells(a, 6).Offset(c, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 7).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 8).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 76).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Range(Cells(a, 9).Offset(c, 0), Cells(a, 9).Offset(b, 0)).Select Selection.ClearContents Range(Cells(a, 11).Offset(c, 0), Cells(a, 11).Offset(b, 0)).Select Selection.ClearContents Range(Cells(a, 12).Offset(c, 0), Cells(a, 13).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 14).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 15).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 16).Offset(c, 0), Cells(a, 20).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 21).Offset(c, 0), Cells(a, 21).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 22).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With Cells(a, 23).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 24).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 25).Offset(c, 0), Cells(a, 26).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 27).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 28).Offset(c, 0), Cells(a, 29).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 30).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 31).Offset(c, 0), Cells(a, 32).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 33).Offset(c, 0).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 34).Offset(c, 0), Cells(a, 36).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 37).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 38).Offset(c, 0), Cells(a, 38).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 39).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 40).Offset(c, 0), Cells(a, 40).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 41).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 42).Offset(c, 0), Cells(a, 42).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 43).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 44).Offset(c, 0), Cells(a, 44).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 45).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 46).Offset(c, 0), Cells(a, 47).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 48).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 49).Offset(c, 0), Cells(a, 49).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 50).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 51).Offset(c, 0), Cells(a, 51).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 52).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 53).Offset(c, 0), Cells(a, 53).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 54).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 55).Offset(c, 0), Cells(a, 55).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Cells(a, 56).Offset(c, 0).Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 57).Offset(c, 0), Cells(a, 58).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 60).Offset(c, 0), Cells(a, 62).Offset(b, 0)).Select Selection.ClearContents With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10092543 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range(Cells(a, 63).Offset(c, 0), Cells(a, 63).Offset(b, 0)).Select Selection.ClearContents Cells(a, 65).Offset(c, 0).Select Selection.ClearContents Range(Cells(10, 7), Cells(1009, 7)).Select Selection.Copy Cells(10, 75).Select Selection.PasteSpecial Paste:=xlPasteValues Cells(5, 100).Select Selection.Copy Else: End If Next a End Sub