Conditional Formatting

Jammydan

Board Regular
Joined
Feb 15, 2010
Messages
141
Hi, I have the following code that works great.

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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
somethingh like this maybe?:

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

' 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,715
Messages
5,660,458
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top