one script interferes with another

[tropdoug]

I got this script from this site for colouring cells and it works a treat

Public OldRng As Range
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not OldRng Is Nothing Then
        OldRng.Interior.ColorIndex = xlNone
    End If
    Target.Interior.ColorIndex = 6
    Set OldRng = Target
End Sub

It is placed in the worksheet code. However in a module I also have this script for inserting a new line at the top of my range and then formattiiong it for the data, finally opening up a user form.

Sub StartNewJob_Click()
Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Range("A5:M5").Select
    Selection.Copy
    Range("A4").Select
    ActiveSheet.Paste
    Range("A4:H4").Select
    Selection.ClearContents
    Range("J4").Select
    Selection.ClearContents
    Range("L4").Select
    Selection.ClearContents
    Range("A4").Select
 
 
MainRecForm.Show
End Sub

I have found that the second script hangs when it gets to the line " ActiveSheet.Paste" with the error message "Paste method of Worksheet class failed"

each script works when the other is removed.

What do I need to do so as to get them both to work?

 

[Michael M]

Sorry, didn't read the whole message
2nd block of code works for me

Sub StartNewJob_Click()
Rows("4:4").Insert Shift:=xlDown
    Range("A5:M5").Copy
    Range("A4").PasteSpecial Paste:=xlPasteFormats
    Range("A4").Select
Application.CutCopyMode = False
MainRecForm.Show
End Sub

 

[Michael M]

Just tried it with a form and seems OK too.

 

[tropdoug]

Thanks it works well for me too. So that I can learn what was the problem? I am very new to VBA, but I can see you haven't used any 'select' commands, other than the last one and the paste command is cleaner I guess. Can you tell me what the "Activesheet.paste" bit was doing (or not doing rather) and why.

Thanks very much for the help.

 

[tropdoug]

Ah just found one issue. If a user uses the add new job macro and then for whatever reason decided to delete the row created, as soon as you try to move cell, this error comes up "Run time error object required"

 

[Michael M]

Yep, I can understand that.
You probably need to rethink your strategy on your worksheet_selection change event.
anytime you record a macro, you can usually delete the "Select.Selection" part and run the lines together.