“For” loop is used in a macro whenever you have an action that needs to be performed a set number of times. For example, if you want to change the fill color of the first 20 cells in a list, you could have the macro select the range and simply fill as desired. But what if you wanted to only fill the cells if they were blank, for example? Telling the macro to select the whole range and fill it wouldn’t work.
This is where a “For” loop is useful. You can have the macro to go down your list, and determine if the cell is blank. If it is, then have the macro fill the cell with a color, otherwise go to the next cell, until it has run through the whole list.
The following code will do exactly what was mentioned above.
- The first line identifies the range of rows to be evaluated. It also tells the macro, that if ‘x’ is less than 20, keep on going.
- The next line tells the macro to select the cell in column 1.
- The ‘If’ section determines if the selected cell is blank, and if it is, it will fill it with a color (in this case 65535=yellow), and if it isn’t blank then just move on and evaluate the rest of the cells until the end of the range.