Sub Worksheet_Change code not working

Question

Any ideas why this code is not working? The portion in red works perfect as a stand alone macro, but not when added to the Worksheet_Change event code. I'm unsure if this makes a difference, but this is a table, so I've also tried referencing the table as the range, but that also did not work. I need to automate this because users are copying/pasting data from other sources, which messes up the formatting. They are not Excel savvy so they do not know how to paste values or correct the formatting issues.

Private Sub Worksheet_Change(ByVal Target As Range)

'

Application.ScreenUpdating = False 

Application.EnableEvents = False 

Application.DisplayAlerts = False 

Set KeyCells = Range("$C$7:$F$52") 

Set PrevCell = ActiveCell 

If Not Application.Intersect(KeyCells, Range(Target.Address)) \_ 

       Is Nothing Then 

ActiveSheet.Unprotect 

Range("K8:O8").Copy 

Range("Table5[[Name]:[Amount Paid]]").Select 

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, \_ 

    SkipBlanks:=False, Transpose:=False 

Application.CutCopyMode = False 

ActiveSheet.Protect 

Application.ScreenUpdating = True 

Application.EnableEvents = True 

Application.DisplayAlerts = True 

End If

End Sub

Answer 1

Nothing happens. You paste data into a cell within the specified range and nothing happens. It's supposed to copy range K08:O8 and paste the formatting in Table 5 (or cells C7:F52).

Answer 2

Hi,

What exactly does "not working" mean? What is happening?