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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,133
Messages
5,509,351
Members
408,728
Latest member
Ramaraju

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top