Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
'Application.EnableEvents = False
Dim White_Cells As String
Dim Green_Cells As String
Dim Gray_Cells As String
Dim Other_Value As String
Dim Focus_Cell As String
Dim MC As String
Dim Disp As String
Dim CandL As String
Dim Labels As String
Dim Disk As String
Dim cal As String
Dim calc As String
Dim scrap_total As String
Dim Filter_paper As String
Dim machine As String
Select Case Range("G2")
Case "PL034", "PL037"
' This code selects clears them and resets the cursor to B5
Range("a8,b5,C5,B8,C8,G8:H12").Select
Selection.ClearContents
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = "Labels"
Range("G12").Value = ""
Range("B8").Value = "=D5*8"
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.18575"
Range("A8").Value = "FP 320"
Range("B7").Value = "Total # of Cups Made"
'This selects the cells to color Green
Range("B5,C5,C8,H8:H11").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5,B8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("G8:G12,H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B5").Select
Case "PL124 Short Filter", "PL125 Short Filter", "PL126 Short Filter", "PL127 Short Filter"
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = "Labels"
Range("G12").Value = ""
Range("B8").Value = "=D5*8"
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.25"
Range("A8").Value = "FP 400 Short Filter"
Range("B7").Value = "Total # of Cups Made"
'This selects the cells to color Green
Range("B5,C5,C8,H8:H11").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5,B8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("G8:G12,H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B5").Select
Case "PL124 Long Filter", "PL125 Long Filter", "PL126 Long Filter", "PL127 Long Filter"
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = "Labels"
Range("G12").Value = "Disk"
Range("B8").Value = "=D5*8"
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.31"
Range("A8").Value = "FP 400 Long Filter"
Range("B7").Value = "Total # of Cups Made"
'This selects the cells to color Green
Range("B5,C5,C8,H8:H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5,B8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("G8:G12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B5").Select
Case "PL118", "PL123"
Range("G8").Value = "Club Cases"
Range("G9").Value = "Cups/Lids"
Range("G10").Value = ""
Range("G11").Value = ""
Range("G12").Value = ""
Range("B8").Value = "=D5*16"
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.18575"
Range("A8").Value = "FP 700"
Range("B7").Value = "Total # of Cups Made"
'This selects the cells to color Green
Range("B5,C5,C8,H8:H9").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5,B8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("G8:G12,H10:H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B5").Select
Case "PL108", "PL116"
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = ""
Range("G12").Value = ""
Range("B8").Value = "=D5*12"
Range("D8").Value = "=B8-C8"
Range("E8").Value = ""
Range("A8").Value = "Optima 720"
Range("B7").Value = "Total # of Cups Made"
'This selects the cells to color Green
Range("B5,C5,C8,H8:H10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5,B8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("G8:G12,H11:H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B5").Select
Case "PL058 Short Filter", "PL083 Short Filter", "PL079 Short Filter"
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = ""
Range("G12").Value = ""
Range("B8").Value = ""
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.0909"
Range("A8").Value = "OPEM 400"
Range("B7").Value = "Total# of Cups Requested"
'This selects the cells to color Green
Range("B8,C8,H8:H10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("B5,C5,G8:G12,H11:H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B8").Select
Case "PL058 Long Filter", "PL083 Long Filter", "PL079 Long Filter"
Range("G8").Value = "Master Cases"
Range("G9").Value = "Dispensers"
Range("G10").Value = "Cups/Lids"
Range("G11").Value = "Labels"
Range("G12").Value = "Disk"
Range("B8").Value = ""
Range("D8").Value = "=B8-C8"
Range("E8").Value = "=D8*0.1133"
Range("A8").Value = "OPEM 400"
Range("B7").Value = "Total# of Cups Requested"
'This selects the cells to color Green
Range("B8,C8,H8:H12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This turns the cells White
Range("D5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("B5,C5,G8:G12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("B8").Select
End Select
'Application.EnableEvents = True
End Sub
LinkBack URL
About LinkBacks
madhatter40 
Register To Reply
Bookmarks