By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

How do I get this Loop to stop

P: 31
I have written this to clear cells coloured 56 in the target row from columns A to L when a Message box is OK'd. But how do I stop it from carrying on and on?
Expand|Select|Wrap|Line Numbers
  1. Dim Rw As String
  2. Rw = Target.Row
  3. Dim Ar As String
  4. Ar = "A" & Rw & ":" & "L" & Rw
  5. Dim Rg As Range
  6. Set Rg = Range(Ar)
  7.  
  8. For Each cl In Rg
  9.  
  10.         With cl
  11.             If .Offset(0, 0).Interior.ColorIndex = 56 Then
  12.                 .ClearContents
  13.             End If
  14.         End With
  15.  
  16.     Next cl
  17.  
  18. End Sub
Thanks.
Apr 12 '12 #1
Share this Question
Share on Google+
1 Reply


P: 31
Sorry again. Figured it out. I am not having to escape the loop. The code keeps going round because the worksheet change events are fired.

Expand|Select|Wrap|Line Numbers
  1. Application.EnableEvents=False
  2. 'then later 
  3. ....True 
cures it
Apr 12 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.