Creating Loops in Excel VBA

Question

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

z32a7ul · Accepted Answer · 2019-04-09 09:26:12Z

2

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.

edited Apr 9, 2019 at 9:26

answered Apr 9, 2019 at 9:18

z32a7ul

3,8474 gold badges24 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cistrat Over a year ago

Thank you! That worked immediately! Do you know if there is a way for me to programme a function that will read the colour of those cells in each row and depending if all the colours are green (38) change the colour of another cell? thank you again

z32a7ul Over a year ago

The Range.Interior.ColorIndex property you already use is readable, too. If you want it in RGB, then you need Range.Interior.Color

Collectives™ on Stack Overflow

Creating Loops in Excel VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related