CODEWITHSUNDEEP
excelvbaloops
cistrat
cistrat

Reputation: 7

Creating Loops in Excel VBA

Hi Im trying to create a loop with an if function that reads data and then formats the colour of the cell depending on a result. I can get the loop to work from the starting point but it wont continue to loop throughout my whole table. Is there a way to resolve this? 

Sub Loop
Range("G6:AM37").Interior.ColorIndex = 0  'No fill colour for all cells in the range to clear the last colouring

Dim ColRange As Range

For RR = 1 To 33   'Loop through Rows
 For CC = 1 To 31  'Loop through col.
    ''''''''''''''''''''''''''''''''''''''''''
    Dim MM As Variant
    Set ColRange = Cells(RR + 5, CC + 6)
    MM = Mid(ColRange, 9, 2)
        If MM = "" Then
             ColRange.Interior.ColorIndex = 0  ' no colour
             Exit For
         ElseIf CInt(MM) > 39 Then
            ColRange.Interior.ColorIndex = 50
        ElseIf CInt(MM) < 40 Then
            ColRange.Interior.ColorIndex = 38
        End If
    ''''''''''''''''''''''''''''''''''''''''''
    Next
Next

Upvotes: 0

Views: 126

Answers (1)

z32a7ul
z32a7ul

Reputation: 3777

Just get rid of the Exit For. At the first cell that fulfills the first If, you leave the loop.

Also note, that you can have loop variables starting from 6, so you don't have to write RR + 5 when referring to row numbers inside the loop.

It's not part of the question, but after making the change I proposed, most probably you will get an error when trying to convert MM to Int. If MM is a String, then CInt(MM) will throw an error. So, before evaluating CInt(MM), you should have a branch, If IsNumeric(MM) = False and invent a formatting for this case (or do nothing in this case).

Also, Integer is a small data type, use Long instead of it. You can convert to Long with CLng.

Upvotes: 2

Related Questions