Jammydan
Board Regular
- Joined
- Feb 15, 2010
- Messages
- 141
Hi, I have the following code that works great.
Howver i need to combine it with other code as below and I can't get it to work.
Can anybody give me some pointers.
Any help greatly appreciated.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Define the range to check
Set ColourMagic = Range("N5:N500")
' 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("A" + CStr(Cell.Row) + ":S" + CStr(Cell.Row)).Interior.ColorIndex = 35
ElseIf Cell.Value = "Declined" Then ' Color it coral
Range("A" + CStr(Cell.Row) + ":Z" + CStr(Cell.Row)).Interior.ColorIndex = 22
ElseIf Cell.Value = "App Recd/In Progress" Then ' Color it shy blue
Range("A" + CStr(Cell.Row) + ":Z" + CStr(Cell.Row)).Interior.ColorIndex = 33
ElseIf Cell.Value = "Not Proceeding" Then ' Color it light orange
Range("A" + CStr(Cell.Row) + ":Z" + CStr(Cell.Row)).Interior.ColorIndex = 45
Else ' Remove all color
Range("A" + CStr(Cell.Row) + ":Z" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If
Next
End Sub
Howver i need to combine it with other code as below and I can't get it to work.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 And LCase(Target.Value) = "declined" Then
Application.EnableEvents = False
With Target.EntireRow.Copy
Sheets("Print").Select
ActiveSheet.Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.HorizontalAlignment = xlLeft
Sheets("Print").Range("A1").Select
ActiveSheet.PageSetup.PrintArea = "$A$2:$B$21"
End With
Application.EnableEvents = True
Application.Dialogs(xlDialogPrint).Show
End If
End Sub
Can anybody give me some pointers.
Any help greatly appreciated.