Loops and VBA go together, and for a good reason too.
Heres all it’s crucial that you know about this quintessential loop.
The object in the for each loop can be cells, ranges, sheets, and even workbooks.
Such is the beauty of this loop; it gives you the flexibility to work with different Excel collections.
The loop cycles through each collection value and stores the reference in the defined variable name.
Let’s understand the code structure with a basic example.
The loop runs till it reaches cell A10.
These are cells, sheets, and workbooks.
Here’s how it’s possible for you to use the loop with all three object types.
key in in the following code within a sub-routine.
Thewithfunction is useful when performing multiple functions with one specific object.
The loop equates c’s value with each cell value and inputs the value as 10.
Additionally, it changes the cell’s color to red, bolds the value, and strikes it through.
Once it completes all the steps, it moves to the next defined value in the range.
How about changing the name of Sheet1 to Sheet3 using VBA?
If it encounters the name Sheet1, it changes it to Sheet3.
It progresses through the remaining sheets, if any, within the workbook.
Once the code execution reaches the last sheet, it exits the loop and the sub-routine.
Let’s demonstrate this function with an example.
Let’s change the cell’s background color based on the values of the cell.
There are a few random numbers in cells A1:A20.
If the cell value exceeds 10, it should turn green.
There are a variety of helpful loops which let you perform different functions with ease.
