Activecell problem
 

Hi
I am trying to have a piece of code run if a value in a range is changed.
What I have at the moment fails because the activecell when my last cell in
the range K20 is changed, the focus moves to the next cell (in this case
C30).

Current code :-

If Not Intersect(Activecell, Range("C14:K16, C20:K22")) Is Nothing Then

****Code*****

End if

How can I adjust to include any alteration to K22.

Also :-

I have a function EmptyRng :-

Function EmptyRng(myRng As Range)
Dim cella As Range
EmptyRng = False
For Each cella In myRng
If IsEmpty(cella) Then
EmptyRng = True
Exit Function
End If
Next cella
End Function

Ideally I would like the above code to run only if the Range("C14:K16,
C20:K22") has no empty cells.

I have tried :-

If Not Intersect(Activecell, Range("C14:K16, C20:K22")) Is Nothing _
And Not EmptyRng(Range("C14:K16, C20:K22") Then

***** Code *****

End If

But I get a Type Mismatch.

Thanks in advance for any assistance
Sandy

Activecell problem
 

If you use the Worksheet Change event, you can test Target (which contains
the range of the change) rather than activecell.
--
Gary''s Student - gsnu2007k


"Sandy" wrote:

Hi
I am trying to have a piece of code run if a value in a range is changed.
What I have at the moment fails because the activecell when my last cell in
the range K20 is changed, the focus moves to the next cell (in this case
C30).

Current code :-

If Not Intersect(Activecell, Range("C14:K16, C20:K22")) Is Nothing Then

****Code*****

End if

How can I adjust to include any alteration to K22.

Also :-

I have a function EmptyRng :-

Function EmptyRng(myRng As Range)
Dim cella As Range
EmptyRng = False
For Each cella In myRng
If IsEmpty(cella) Then
EmptyRng = True
Exit Function
End If
Next cella
End Function

Ideally I would like the above code to run only if the Range("C14:K16,
C20:K22") has no empty cells.

I have tried :-

If Not Intersect(Activecell, Range("C14:K16, C20:K22")) Is Nothing _
And Not EmptyRng(Range("C14:K16, C20:K22") Then

***** Code *****

End If

But I get a Type Mismatch.

Thanks in advance for any assistance
Sandy