Fire A Change Event Only When a Cell = 1 in a Range

[rye123]

Hi Forum,

Somewhat new to VBA. Got the basics down and I am starting to wade out to the deeper end of the pool. So sorry if my question is very elementary.

I have been playing around with how to Fire A Change Event Only When a Cell = 1 in a Range but I cannot seem to get it to work.

Here is what I have so far:

Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Set WatchRange = Range("J3:j100")
Set IntersectRange = Intersect(Target, WatchRange)
If IntersectRange Is = 1 Then
Call celltestone 'testmarco
Else
'Do nothing; don't care if the value of the cell here is zero, 2, "" or any other positive value (don't want negative values thou).
End If
End Sub


Marco works fine so that is not the issue. I just want the code to fire when the cell = 1.

How am I firing the cell? With something simple like this in cell J3, for example =if(b3>a3, 1, 0). i copy the formula down to j100. Once the marco fires in any of the 97 cells it:

-Does some work
-Deletes the contents of the specific cell that fired
-Resets the cell that fired with the formula waiting for the next firing event.

Again the macro works fine so that is not the problem.

Any help is much appreciated.

[MarvinP]

Hi rye123 and welcome to the forum,

Because you are changing the cell that fired the event, this might be a problem. You might need to do a:
Application.EnableEvents = False
to turn off events from fireing
Then at the bottom of the code turn events back on with a:
Application.EnableEvents = True

Hope that works. If not then keep asking.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[rye123]

So Very awesome; it worked! Thanks!

[rye123]

I celebrated just a bit too early.

(Still celebrating thou).

The cell that changes is the one directly below the one that I should change. How do i change the code so j3 is affected and not j4 for example?

thanks again for the awesome help!

[JOHN H. DAVIS]

You're welcome, If I understand you correctly.

Please Login or Register  to view this content.