Multiple Function issue

[sryan32]

I am fairly new to VBA and am having an issue that I cannot figure out. The first section of my code provides conditional formatting based on the items in the third columns drop down menu. This section of code is working fine. The second section of code is meant to take a row that has "completed" in the 3rd column, and copy the entire row and paste it to the second sheet "Completed". I have this code working in a seperate file, but when I try to combine the two functions I can't get it to work. Any thoughts? Thanks in advance for any help.

[JBeaucaire]

Try this, remove all the other code and replace with:

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
 
Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
On Error GoTo ErrorExit
    
    For Each Cell In Target
        Select Case Cell.Value
            Case vbNullString
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
            Case "URGENT"
                Cell.Interior.ColorIndex = 3
                Cell.Font.Bold = True
            Case "Incoming"
                Cell.Interior.ColorIndex = 17
                Cell.Font.Bold = False
            Case "To Do"
                Cell.Interior.ColorIndex = 6
                Cell.Font.Bold = True
            Case "Outgoing"
                Cell.Interior.ColorIndex = 46
                Cell.Font.Bold = False
            Case "Completed"
                Cell.EntireRow.Copy
                Worksheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Cell.EntireRow.Delete
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
    
ErrorExit:
Application.EnableEvents = True
End Sub

[sryan32]

Yes works perfectly. Thanks for your help.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.


====
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)