intersect function problem
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")
--
Regards,
Tom Ogilvy
"pumpbhoy" wrote in message
...
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
|