Thread: vba range issue

hello
i have a code that searches column B for data entered in textbox, when the data is located it highlights active row yellow, i then added the following code so that when i changed from highlighted row the row changed back to no fill. it worked but now i can not cut and paste within the sheet? can this be fixed.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("$B:$I").Interior.ColorIndex = xlNone
    Intersect(Target.EntireRow, Range("$B:$I")).Interior.ColorIndex = xlNone
    Range("B5:I5").Interior.ColorIndex = 34
    Range("B9:I9").Interior.ColorIndex = 35
End Sub

Hello waltron,

Welcome to the Forum!

What lead you to believe this routine is causing the problem?

g'day Leith

when i removed the above mentioned code i could cut and paste. i have another code (posted below) that also stops working when this code is active. i get a run time error "1004" "paste method of worksheet failed" i can't put my finger on it?

Code:

  Sub addjob()

    Rows("7:7").Select
    Selection.Copy
    Rows("102:102").Select
    ActiveSheet.Paste
    Rows("11:102").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("C11"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Rows("7:7").Select
    Selection.ClearContents
    Range("B7").Select
    ActiveWorkbook.save
End Sub

Cheers.

Hello waltron,

I don't know why your workbook is all buggered up but give this a try.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Rng As Range
    Range("$B:$I").Interior.ColorIndex = xlNone
      Set Rng = Intersect(Target.EntireRow, Range("$B:$I"))
        If Not Rng Is Nothing Then
           Rng.Interior.ColorIndex = xlNone
           Range("B5:I5").Interior.ColorIndex = 34
           Range("B9:I9").Interior.ColorIndex = 35
        End If
End Sub