Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And LCase(Target.Value) = "declined" Then
Application.EnableEvents = False
With Target.EntireRow.Copy
Sheets("Declined Cover Sheet").Visible = True
Sheets("Declined Cover Sheet").Select
ActiveSheet.Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.HorizontalAlignment = xlLeft
Sheets("Declined Cover Sheet").Range("A1").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$C$23"
End With
Application.EnableEvents = True
Application.Dialogs(xlDialogPrint).Show
Sheets("Declined Cover Sheet").Visible = False
Sheets("Master Sheet").Select
End If
If Target.Column = 3 And LCase(Target.Value) = "not proceeding" Then
Application.EnableEvents = False
With Target.EntireRow.Copy
Sheets("Not Proceeding Cover Sheet").Visible = True
Sheets("Not Proceeding Cover Sheet").Select
ActiveSheet.Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.HorizontalAlignment = xlLeft
Sheets("Not Proceeding Cover Sheet").Range("A1").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$C$23"
End With
Application.EnableEvents = True
Application.Dialogs(xlDialogPrint).Show
Sheets("Not Proceeding Cover Sheet").Visible = False
Sheets("Master Sheet").Select
End If
' Define the range to check
Set ColourMagic = Range("C5:C500")
' Start checking each cell in the target range named ColorMagic and nothings
For Each Cell In ColourMagic
If Cell.Value = "Opened" Then ' Color it light green
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 35
ElseIf Cell.Value = "Declined" Then ' Color it coral
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 22
ElseIf Cell.Value = "App Recd/In Progress" Then ' Color it ice blue
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 24
ElseIf Cell.Value = "Not Proceeding" Then ' Color it tan
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 40
ElseIf Cell.Value = "Pending" Then ' Color it ivory
Range("B" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 19
Else ' Remove all color
Range("A" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If
Next
End Sub