Results 1 to 8 of 8

Thread: Mutliple Loops

  1. #1

    Mutliple Loops



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm totally new to Excel and VBA. I hope the title I used is accurate.

    I created an excel (2010) spreadsheet that will highlight a row (1 thru 13) if a condition is met in column 1. Everything works until I put a second FOR EACH. At this point, the only FOR EACH that works is the 2nd one. How do I get them all to work? Here is what I got so far...

    Code:
    Dim tCell As Range
        
        For Each tCell In Target.Cells
            If tCell.Column = 1 Then
                If tCell.Value = "MATT" Then
                    Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,255,0"
                    
                Else
                    Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = xlNone
                End If
            End If
        Next tCell
    
    ''''''''''''''''''''''
    Dim aCell As Range
        
        For Each yCell In Target.Cells
            If yCell.Column = 1 Then
                If yCell.Value = "TEST" Then
                    Range(Cells(yCell.Row, 1), Cells(yCell.Row, 13)).Interior.Color = "255,085,140"
                    
                Else
                    Range(Cells(yCell.Row, 1), Cells(yCell.Row, 13)).Interior.Color = xlNone
                End If
            End If
        Next yCell
    Last edited by Bob Phillips; 2015-02-05 at 10:05 PM. Reason: Added code tags

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    will this do what you are after ?
    Code:
    Dim tCell As Range
    
    If tCell.Column <> 1 Then Exit Sub
    
    For Each tCell In Target.Cells
        If tCell.Value = "MATT" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,255,0"
        ElseIf tCell.Value = "TEST" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,085,140"
        Else
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = xlNone
        End If
    Next tCell

  3. #3
    I get run time error - 91

    Object variable or With block variabe not set. I think I see what you're hinting at tho.. All my IF's would be in 1 loop.

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    That's right Matt, you can check more than 1 thing in a single loop.

    Don't know what the rest of your code is, but if you put your cursor any where within the procedure and use the F8 key you can step through the procedure executing the lines one at a time and see on which line the error is happening. If it's something not obvious to you post the entire procedure and we can trouble-shoot it with you.

  5. #5
    You haven't instantiated tcell.

    Quote Originally Posted by NoS View Post
    will this do what you are after ?
    Code:
    Dim tCell As Range
    
    If tCell.Column <> 1 Then Exit Sub
    
    For Each tCell In Target.Cells
        If tCell.Value = "MATT" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,255,0"
        ElseIf tCell.Value = "TEST" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,085,140"
        Else
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = xlNone
        End If
    Next tCell

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    @ skywriter

    Had to look up "instantiate" and it's not a word I'd use, but.... this fixes the error
    Code:
    Dim tCell As Range
    
    For Each tCell In Target.Cells
        If tCell.Column <> 1 Then Exit Sub
        If tCell.Value = "MATT" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,255,0"
        ElseIf tCell.Value = "TEST" Then
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = "255,085,140"
        Else
            Range(Cells(tCell.Row, 1), Cells(tCell.Row, 13)).Interior.Color = xlNone
        End If
    Next tCell
    Thanks skywriter, and welcome to the forum.
    Is that avatar on the big forum a selfie, are you really.... naa, can't be

  7. #7
    Thanks skywriter, and welcome to the forum.
    Is that avatar on the big forum a selfie, are you really.... naa, can't be
    Thanks for the welcome. It couldn't be a selfie you can see both of my arms, no camera, but yeah it's me.
    Last edited by skywriter; 2015-02-06 at 02:54 AM.

  8. #8
    I tried the STEP INTO and seen where the code broke. Plus I was able to correct it and add more conditions.
    Thank!!!

Posting Permissions

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