none
Stuck in loop, Help RRS feed

  • Question

  • Hi all, I am new to VBA and can't find a way to end this loop. I have tried several solutions but am stuk in a constant loop and have to restart application. The coding works how I want, just want to end infinite loop. Any help would be greatly appreciated. Thanks

    Sub Macro5()
     
       Dim myData As Range
       Dim myCell As Range
      
       Set myData = Range(Cells.Address)
     
       For Each myCell In myData
      
       If myCell.Value Mod 2 = 0 And myCell.Value > 0 Then
      
       myCell.Interior.ColorIndex = 37
      
       ElseIf myCell.Value Mod 2 = 1 Then
      
       myCell.Interior.ColorIndex = 4
      
       End If

       Next

    End Sub


    • Edited by Elvis2015 Tuesday, October 6, 2015 2:09 AM
    Tuesday, October 6, 2015 2:06 AM

All replies

  • Re:  too much loop

    You are looping thru every cell on the worksheet because "cells" refers to every cell on the active worksheet.
    If you prefix "cells" with a range then cells refers to the cells only in that range.
    So, you can limit the loop to the cells you select by using...

    Set myData = Selection.Cells
      instead of...
    Set myData = Range(Cells.Address)
    '---

    FWIW...
    I saw your namesake perform in Lake Tahoe, Nevada, not too long before he died.

    '---
    Jim Cone
    Portland, Oregon USA
    (free & commercial excel add-ins & workbooks)


    • Proposed as answer by James Cone Tuesday, October 6, 2015 10:29 PM
    • Edited by James Cone Thursday, October 20, 2016 3:22 PM
    Tuesday, October 6, 2015 2:48 AM
  • Thank you for your quick reply. Thanks I have solved the issue.
    Tuesday, October 6, 2015 9:27 PM