intersect function problem

[pumpbhoy]

I am trying to get a couple of macros to run when a cell value changes. I can
get this to work but I have a problem caused by the selection of multiple
cells.

In the following code the macro delete_sheet works fine when a single cell
value in the keycells range is changed to 'No comment', however, the
add_sheet macro has part of its code which selects multiple cells in other
ranges and edits them. As soon as the add_sheet macro gets to this point I
get the error 'Run time error 13 - type mismatch'.

Same error occurs if multiple cells are selected manually and I try to edit
them.

The following code is embedded in the worksheet while the other 2 macros are
modules.

Any advice greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown))

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "No Comment" Then

delete_sheet

ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "See Comments Provided" Then

add_sheet

End If
End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
if Target.count = 1 then exit sub
Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown))

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "No Comment" Then

delete_sheet

ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "See Comments Provided" Then

add_sheet

End If
End Sub

Using Range(Target.Address) is like saying Range(Range("A1").Address) to
refer to cell A1.
In otherwords, just use Target since it is a reference to a range. Same as
if you did

set Target = Range("A1")

 

[pumpbhoy]

Thanks for this. I have got it working ok now.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
if Target.count = 1 then exit sub
Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown))

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "No Comment" Then

delete_sheet

ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing And Range(Target.Address) = "See Comments Provided" Then

add_sheet

End If
End Sub

Using Range(Target.Address) is like saying Range(Range("A1").Address) to
refer to cell A1.
In otherwords, just use Target since it is a reference to a range. Same as
if you did

set Target = Range("A1")