Solved Getting Error Intersect of Object Globad Failed

[rehanemis]

I am trying to delete record but getting error while clicking on cell .
Code added on Sheet2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("q:q")) Is Nothing Then
    If ActiveCell.Value = "Add to Orders List" Then
        rowNum = Target.Cells.Row
        cellval = WorksheetFunction.Sum(Sheet2.Range("E" & rowNum + 3 & ":O" & rowNum + 3))
        If cellval = 0 Then
            MsgBox "Please Add Number of Order/s (Yellow Cells)", vbInformation, "Input Required"
            Sheet2.Range("E" & rowNum + 3 & ":O" & rowNum + 3).Select
        Else
            id = Range("A" & rowNum).Value
                Set FindRow = Sheet3.Range("A:A").Find(What:=id, LookIn:=xlValues, LookAt:=xlWhole) 'Searching for Combobox1 value
                    If Not (FindRow Is Nothing) Then
           'checking if same product is already added
                    MsgBox "Already Added"
             Else
                        lastRow = Sheet3.Range("A10000").End(xlUp).Row 'Getting last row in orders sheet
                    If lastRow < 3 Then
                        Sheet2.Range(rowNum & ":" & rowNum + 4).Copy Sheet3.Range("A" & lastRow + 2)
                        Sheet3.Range("Q" & lastRow + 2).Value = "DELETE"
                        Sheet2.Range("O" & rowNum + 1).Select
                    ElseIf lastRow >= 3 Then
                        Sheet2.Range(rowNum & ":" & rowNum + 4).Copy Sheet3.Range("A" & lastRow + 6)
                        Sheet3.Range("Q" & lastRow + 6).Value = "DELETE"


                        Sheet2.Range("q" & rowNum + 1).Select
                    End If
            End If
        End If
    End If

End If
End Sub

On Sheet3 I have code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("Q:Q")) Is Nothing Then
    If ActiveCell = "DELETE" Then
        MsgBox "Delete is clicked"
    End If

End If
End Sub

When I click on Sheet2 on specific cell there is error. Error 1004 " Method Intersect of Object Global Failed"
Any solution ?

 

[cheekybuddha]

Replace all instances of 'ActiveCell' with 'Target':

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("q:q")) Is Nothing Then
    If Target.Value = "Add to Orders List" Then
' ...'

 

[rehanemis]

After changing to Target it thronging error "Error 13" Type mismatch. and highlight the "If Target.Value = "Add to Orders List" Then"

 

[Gasman]

So what is the address and value in Target?

 

[cheekybuddha]

After changing to Target it thronging error "Error 13" Type mismatch. and highlight the "If Target.Value = "Add to Orders List" Then"

You may have selected more than one cell.

Add a check for Target.Cells.Count = 1 before trying to access its value (untested)

 

[rehanemis]

I tried on single cell not on merged one but no luck.
The target value I am looking for is in column Q so when I click on the cell of column Q program should check the cell value and do action accordingly.

 

[cheekybuddha]

Take it one step at a time:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("q:q")) Is Nothing Then
    MsgBox Target.Address & ": " & Target.Value
  End If
End Sub

 

[Darrell]

If ActiveCell = "DELETE" Then

On your Sheet3 code, I think this bit should have been

If ActiveCell.Text = "DELETE" Then

 

[rehanemis]

Thanks for all to reply me on this.
using application.enableevent=false and then true works fine.

 

[Isaac]

FYI you can also simply test for the column property and or the row property of the target. I find this slightly simpler to read and type but either way works you can do the intersect too if you prefer.
If target.column=5, etc