Page 20 of 24PREVNEXT

Get in the loop with Excel macros

A For Each...Next loop in action

This For Each…Next Loop macro reads each cell and colors it according to the contents.

With this macro, you could set the background colors of cells in a selected range. The background will be red if a cell has the word "book" in it, green if it has the word "movie" in it, blue if it contains any other value, and clear if the cell is blank.

This process could be accomplished by using the Conditional Formatting feature. However, this code example shows how to operate on all cells in a selected range with a loop.

Sub LoopRange2()

"MyCell" is a variable that tracks the cell the loop is acting on. The asterisks in the code example allow the code to find the specified text when it is part of more text. As you see in the illustration, this code does not search only for text starting with capital letters. It finds both "Book" and "Read the book." To obtain this behavior, you would need to specify it before entering the macro.

At the very beginning of the module, before typing Sub or pasting any other code, you would enter the following code:

Option Compare Text

This option specifies that any following code using the Like operator will ignore the case of text it acts on. Without this option, the code example would change background color only when it found text starting with a capital letter.

Page 20 of 24PREVNEXT