+ Reply to Thread
Results 1 to 5 of 5

How to stop a loop at a blank cell

Hybrid View

  1. 09-25-2007, 06:06 PM #1
    sunandm00n
    sunandm00n is offline
    Registered User
    Join Date
    09-25-2007
    Posts
    5

    Question How to stop a loop at a blank cell

    So I've been trying to solve this problem for a while now... a few weeks. I'm trying to get this code to run down a column and fill the row in a certain color (not the entire row though, just the cells with text in them - if there is a better way to do it that would be great to know also! ) and STOP WHEN IT GETS TO A BLANK CELL!!! This seems to be a common theme, but I can't get the code from any of the other postings to work. No matter what I try, I just can't get it to stop! A bright and shiney gold star to anyone who can help...

    Here is my code:
    Sub Metrics()

    'Set MyCell as the range
    Dim MyCell As Range
    x = 2
    Columns("F:F").Select
    Do While Cells(x, 6).Value <> ""
    'Loop using a For Each…Next in selection
    For Each MyCell In Selection
        If MyCell.Value = "" Then
            'Clear the cell background color
            MyCell.Interior.ColorIndex = xlNone
                                        
        ElseIf MyCell.Value Like "*Go to*" Then
            'Set the cell background color to dark orange
            MyCell.Interior.ColorIndex = 46
            MyCell.Offset(0, -5).Interior.ColorIndex = 46
            MyCell.Offset(0, -4).Interior.ColorIndex = 46
            MyCell.Offset(0, -3).Interior.ColorIndex = 46
            MyCell.Offset(0, -2).Interior.ColorIndex = 46
            MyCell.Offset(0, -1).Interior.ColorIndex = 46
            MyCell.Offset(0, 1).Interior.ColorIndex = 46
                    
        ElseIf MyCell.Value Like "*Merged as child*" Then
            'Set the cell background color to light yellow
            MyCell.Interior.ColorIndex = 36
            MyCell.Offset(0, -5).Interior.ColorIndex = 36
            MyCell.Offset(0, -4).Interior.ColorIndex = 36
            MyCell.Offset(0, -3).Interior.ColorIndex = 36
            MyCell.Offset(0, -2).Interior.ColorIndex = 36
            MyCell.Offset(0, -1).Interior.ColorIndex = 36
            MyCell.Offset(0, 1).Interior.ColorIndex = 36
                                                       
        ElseIf MyCell.Value Like "*Approve with Mod*" Then
            'Set the cell background color to light green
            MyCell.Interior.ColorIndex = 35
            MyCell.Offset(0, -5).Interior.ColorIndex = 35
            MyCell.Offset(0, -4).Interior.ColorIndex = 35
            MyCell.Offset(0, -3).Interior.ColorIndex = 35
            MyCell.Offset(0, -2).Interior.ColorIndex = 35
            MyCell.Offset(0, -1).Interior.ColorIndex = 35
            MyCell.Offset(0, 1).Interior.ColorIndex = 35
            
        ElseIf MyCell.Value Like "*Approve*" Then
            'Set the cell background color to light periwinkle
            MyCell.Interior.ColorIndex = 34
            MyCell.Offset(0, -5).Interior.ColorIndex = 34
            MyCell.Offset(0, -4).Interior.ColorIndex = 34
            MyCell.Offset(0, -3).Interior.ColorIndex = 34
            MyCell.Offset(0, -2).Interior.ColorIndex = 34
            MyCell.Offset(0, -1).Interior.ColorIndex = 34
            MyCell.Offset(0, 1).Interior.ColorIndex = 34
            
        ElseIf MyCell.Value Like "*Withdrawal*" Then
            'Set the cell background color to light purple
            MyCell.Interior.ColorIndex = 39
            MyCell.Offset(0, -5).Interior.ColorIndex = 39
            MyCell.Offset(0, -4).Interior.ColorIndex = 39
            MyCell.Offset(0, -3).Interior.ColorIndex = 39
            MyCell.Offset(0, -2).Interior.ColorIndex = 39
            MyCell.Offset(0, -1).Interior.ColorIndex = 39
            MyCell.Offset(0, 1).Interior.ColorIndex = 39
            
        ElseIf MyCell.Value Like "*Disposition*" Then
            'Set the cell background color to lake blue
            'MyCell.Interior.ColorIndex = 33
        
        Else
            'Set the cell background color to gray
            MyCell.Interior.ColorIndex = 15
            MyCell.Offset(0, -5).Interior.ColorIndex = 15
            MyCell.Offset(0, -4).Interior.ColorIndex = 15
            MyCell.Offset(0, -3).Interior.ColorIndex = 15
            MyCell.Offset(0, -2).Interior.ColorIndex = 15
            MyCell.Offset(0, -1).Interior.ColorIndex = 15
            MyCell.Offset(0, 1).Interior.ColorIndex = 15
        End If
        
    Next
    Loop
End Sub
    Last edited by sunandm00n; 09-25-2007 at 06:36 PM. Reason: illegal title, wrap code
    Reply With QuoteRegister To Reply
  2. 09-25-2007, 07:16 PM #2
    Ikaabod
    Ikaabod is offline
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    How about this?
    Sub Metrics()
    Dim MyCell As Range
    Dim iLastRow As Integer
    iLastRow = Range("F" & Rows.Count).End(xlUp).Row
    For Each MyCell In Range("F1:F" & iLastRow)
        If MyCell.Value = "" Then
            MyCell.Interior.ColorIndex = xlNone
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = xlNone
        ElseIf MyCell.Value Like "*Go to*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 46
        ElseIf MyCell.Value Like "*Merged as child*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 36
        ElseIf MyCell.Value Like "*Approve with Mod*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 35
        ElseIf MyCell.Value Like "*Approve*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 34
        ElseIf MyCell.Value Like "*Withdrawal*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 39
        ElseIf MyCell.Value Like "*Disposition*" Then
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 33
        Else
            Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1)).Interior.ColorIndex = 15
        End If
    Next
End Sub
    Reply With QuoteRegister To Reply
  3. 09-25-2007, 07:21 PM #3
    smart89
    smart89 is offline
    Registered User
    Join Date
    09-24-2007
    Posts
    6
    I modified the code like this ,please try it.hope useful to you.

    Sub Metrics()

    'Set MyCell as the range
    Dim MyCell As Range
    x = 1
    Columns("F:F").Select
    'Do While Cells(x, 6).Value <> ""
    'Loop using a For Each…Next in selection
    For Each MyCell In Selection
        If MyCell.Value = "" Then
            'Clear the cell background color
            MyCell.Interior.ColorIndex = xlNone
                                        
        ElseIf MyCell.Value Like "*Go to*" Then
            'Set the cell background color to dark orange
            MyCell.Interior.ColorIndex = 46
            MyCell.Offset(0, -5).Interior.ColorIndex = 46
            MyCell.Offset(0, -4).Interior.ColorIndex = 46
            MyCell.Offset(0, -3).Interior.ColorIndex = 46
            MyCell.Offset(0, -2).Interior.ColorIndex = 46
            MyCell.Offset(0, -1).Interior.ColorIndex = 46
            MyCell.Offset(0, 1).Interior.ColorIndex = 46
                    
        ElseIf MyCell.Value Like "*Merged as child*" Then
            'Set the cell background color to light yellow
            MyCell.Interior.ColorIndex = 36
            MyCell.Offset(0, -5).Interior.ColorIndex = 36
            MyCell.Offset(0, -4).Interior.ColorIndex = 36
            MyCell.Offset(0, -3).Interior.ColorIndex = 36
            MyCell.Offset(0, -2).Interior.ColorIndex = 36
            MyCell.Offset(0, -1).Interior.ColorIndex = 36
            MyCell.Offset(0, 1).Interior.ColorIndex = 36
                                                       
        ElseIf MyCell.Value Like "*Approve with Mod*" Then
            'Set the cell background color to light green
            MyCell.Interior.ColorIndex = 35
            MyCell.Offset(0, -5).Interior.ColorIndex = 35
            MyCell.Offset(0, -4).Interior.ColorIndex = 35
            MyCell.Offset(0, -3).Interior.ColorIndex = 35
            MyCell.Offset(0, -2).Interior.ColorIndex = 35
            MyCell.Offset(0, -1).Interior.ColorIndex = 35
            MyCell.Offset(0, 1).Interior.ColorIndex = 35
            
        ElseIf MyCell.Value Like "*Approve*" Then
            'Set the cell background color to light periwinkle
            MyCell.Interior.ColorIndex = 34
            MyCell.Offset(0, -5).Interior.ColorIndex = 34
            MyCell.Offset(0, -4).Interior.ColorIndex = 34
            MyCell.Offset(0, -3).Interior.ColorIndex = 34
            MyCell.Offset(0, -2).Interior.ColorIndex = 34
            MyCell.Offset(0, -1).Interior.ColorIndex = 34
            MyCell.Offset(0, 1).Interior.ColorIndex = 34
            
        ElseIf MyCell.Value Like "*Withdrawal*" Then
            'Set the cell background color to light purple
            MyCell.Interior.ColorIndex = 39
            MyCell.Offset(0, -5).Interior.ColorIndex = 39
            MyCell.Offset(0, -4).Interior.ColorIndex = 39
            MyCell.Offset(0, -3).Interior.ColorIndex = 39
            MyCell.Offset(0, -2).Interior.ColorIndex = 39
            MyCell.Offset(0, -1).Interior.ColorIndex = 39
            MyCell.Offset(0, 1).Interior.ColorIndex = 39
            
        ElseIf MyCell.Value Like "*Disposition*" Then
            'Set the cell background color to lake blue
            'MyCell.Interior.ColorIndex = 33
        
        Else
            'Set the cell background color to gray
            MyCell.Interior.ColorIndex = 15
            MyCell.Offset(0, -5).Interior.ColorIndex = 15
            MyCell.Offset(0, -4).Interior.ColorIndex = 15
            MyCell.Offset(0, -3).Interior.ColorIndex = 15
            MyCell.Offset(0, -2).Interior.ColorIndex = 15
            MyCell.Offset(0, -1).Interior.ColorIndex = 15
            MyCell.Offset(0, 1).Interior.ColorIndex = 15
        End If
        x = x + 1
        If Cells(x, 6) = "" Then Exit For
    Next
    'Loop
End Sub
    Reply With QuoteRegister To Reply
  4. 09-25-2007, 07:32 PM #4
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello sunandm00n,

    I shortened your code, and it should work faster also. 
    Sub Metrics()

    'Set MyCell as the range
    Dim MyCell As Range
    'Range of cells whose color will be changed
    Dim MyRng As Range
    Dim LastRow As Long, StartRow As Long
    
    StartRow = 2
    LastRow = Range(Rows.Count, "F").End(xlUp).Row
    
    'Loop using a For Each…Next in selection
    For Each MyCell In Range(Cells(StartRow, "F"), Cells(LastRow, "F"))
      Set MyRng = Range(MyCell.Offset(0, -5), MyCell.Offset(0, 1))
      
        If MyCell.Value = "" Then
            'Clear the cell background color
            MyCell.Interior.ColorIndex = xlColorIndexAutomatic
            Exit Sub
        End If
        
        If MyCell.Value Like "*Go to*" Then
            'Set the cell background color to dark orange
            MyRng.Interior.ColorIndex = 46
                    
        ElseIf MyCell.Value Like "*Merged as child*" Then
            'Set the cell background color to light yellow
            MyRng.Interior.ColorIndex = 36
                                                       
        ElseIf MyCell.Value Like "*Approve with Mod*" Then
            'Set the cell background color to light green
            MyRng.Interior.ColorIndex = 35
            
        ElseIf MyCell.Value Like "*Approve*" Then
            'Set the cell background color to light periwinkle
            MyRng.Interior.ColorIndex = 34
            MyCell.Interior.ColorIndex = 34
            
        ElseIf MyCell.Value Like "*Withdrawal*" Then
            'Set the cell background color to light purple
            MyRng.Interior.ColorIndex = 39
            
        ElseIf MyCell.Value Like "*Disposition*" Then
            'Set the cell background color to lake blue
            'MyCell.Interior.ColorIndex = 33
        
        Else
            'Set the cell background color to gray
            MyRng.Interior.ColorIndex = 15
        End If
        
    Next MyCell
    
End Sub
    Sincerely,
    Leith Ross
    Reply With QuoteRegister To Reply
  5. 09-26-2007, 11:16 AM #5
    sunandm00n
    sunandm00n is offline
    Registered User
    Join Date
    09-25-2007
    Posts
    5

    Talking

    Yeah! Thank you so much everyone! The first two worked beautifully, but I got an error message for Leith's. It said "Run time error '1004': Method 'Range' of object '_Global' failed." When I tell it to debug, it has an issue with the line 
     LastRow = Range(Rows.Count, "F").End(xlUp).Row
    . Thanks so much for the help - it's great to have a code that works and stops!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1