none
How do I iterate a loop from within a conditional? RRS feed

  • Question

  • I am having problems with the following code.  What I am trying to do is match a cell from one sheet to another, and then once a match if found for the first column, to check the subsequent columns and highlight those values that do not match.  Further to this, I am attempting to capture the condition that if the current value from the second sheet does not match any values in the first sheet, it will highlight the column with a different colour.

    The error that I am getting is that I cannot use the Next syntax to iterate through the loop from within the conditional statement it seems.  Am I using the wrong syntax, or is there a workaround to allow for this functionality.  Please review the code reference below.

    Sub SheetCompareAdv()
    
    Dim cellval
    
    For mRow = 13 To 118
        cellval = Sheets("Original").Cells(mRow, 1).Value
        For cRow = 13 To 115
            If cellval <> Sheets("Compare").Cells(cRow, 1).Value Then
                Next cRow
            ElseIf cellval = Sheets("Compare").Cells(cRow, 1).Value Then
                For x = 1 To 7
                    If Sheets("Original").Cells(mRow, x + 1).Value <> Sheets("Compare").Cells(cRow, x + 1).Value Then
                        Sheets("Original").Cells(mRow, x + 1).Interior.ColorIndex = 6
                        Sheets("Compare").Cells(cRow, x + 1).Interior.ColorIndex = 6
                    Else
                        Sheets("Original").Cells(mRow, x + 1).Interior.ColorIndex = xlNone
                        Sheets("Compare").Cells(cRow, x + 1).Interior.ColorIndex = xlNone
                    End If
                    Next mRow
            Else
                Sheets("Original").Cells(mRow, 1).Interior.ColorIndex = 5
                Next mRow
        
    End Sub
    
    

    Thanks for reading.
    Friday, November 25, 2011 3:12 PM

Answers

  • I think something like this might be what you want (you cannot use Next inside a loop to skip to the next iteration of the loop)
    Sub SheetCompareAdv()
    
    Dim cellval
    
    For mRow = 13 To 118
        cellval = Sheets("Original").Cells(mRow, 1).Value
        For cRow = 13 To 115
    
            If cellval = Sheets("Compare").Cells(cRow, 1).Value Then
                For x = 1 To 7
                    If Sheets("Original").Cells(mRow, x + 1).Value <> Sheets("Compare").Cells(cRow, x + 1).Value Then
                        Sheets("Original").Cells(mRow, x + 1).Interior.ColorIndex = 6
                        Sheets("Compare").Cells(cRow, x + 1).Interior.ColorIndex = 6
                    Else
                        Sheets("Original").Cells(mRow, x + 1).Interior.ColorIndex = xlNone
                        Sheets("Compare").Cells(cRow, x + 1).Interior.ColorIndex = xlNone
                    End If
    Next x
     Else Sheets("Original").Cells(mRow, 1).Interior.ColorIndex = 5
    End if
    Next cRow
     Next mRow End Sub


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    Friday, November 25, 2011 6:13 PM