CODEWITHSUNDEEP
vbaexcel
cardsfan_365
cardsfan_365

Reputation: 3

Fill in cells based on the contents of a variable range of cells

In my spreadsheet, I have a list of data in a column that has item numbers. Given a value (either "'1 Quad" or any value A-D) in an adjacent cell, there should be no rows with repeating item numbers or 8 rows with repeating item numbers respectively in the same column. If there are more or less than the required amount of item numbers (too many repeating numbers or missed numbers), then I would like the entire variable range of identical item numbers to highlight (which I am simply doing with Fill).

   For i = 3 To (mainRow - 1)
    k = i
    j = i
    If Range("G3") = "'1 Quad" Then
        If Range("E" & i).Value > (Range("E" & (i + 1)).Value - 1) Then
            Range("E" & i, "E" & (i + 1)).Interior.Color = RGB(255, 0, 0)
            i = i + 1
        End If
        If Range("E" & i).Value < (Range("E" & (i + 1)).Value - 1) Then
            Range("E" & i, "E" & (i + 1)).Interior.Color = RGB(0, 255, 0)
            i = i + 1
        End If
    ElseIf Range("G3").Value = "A" Or "B" Or "C" Or "D" Then
        Do
            If Range("E" & j).Value = Range("E" & (j + 1)).Value Then
                j = j + 1
            End If
            If Range("E" & j).Value <> Range("E" & (j + 1)).Value Then
                If j < 8 Then
                    For k = i To j
                        Range("E" & i).Interior.Color = RGB(255, 0, 0)
                    Next k
                End If

                If j > 8 Then
                    For k = i To j
                        Range("E" & i).Interior.Color = RGB(0, 255, 0)
                    Next k
                End If
                Exit Do
            End If
        While j < (mainRow - 1)
        i = k
    End If

Next i

With This code, I am getting the error "End If without Block If" which does not appear to be the case at all. If I remove the last "End IF", I receive the "Next without For" error. Thanks ahead of time for your help.

**I edited the code to include the end ifs and still receiving the same error.

Upvotes: 0

Views: 109

Answers (1)

Weasemunk
Weasemunk

Reputation: 455

You're missing 3 End Ifs, and your Do-While syntax is off. Try this instead:

    Do While j < (mainRow - 1)
        If Range("E" & j).Value <> Range("E" & (j + 1)).Value Then
            If j < 8 Then
                For k = i To j
                    Range("E" & i).Interior.Color = RGB(255, 0, 0)
                Next k
                Exit Do
            End If
            If j > 8 Then
                For k = i To j
                    Range("E" & i).Interior.Color = RGB(0, 255, 0)
                Next k
                Exit Do
            End If
        End If
    Loop

Upvotes: 2

Related Questions