Conditional Formatting

[Jammydan]

Hi, I have the following code that works great.

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.

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.

 

[bobsan42]

somethingh like this maybe?:

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

' 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

how it fails to work? nothing works at all?

 

[Jammydan]

Works great now, thank you very much