Excel VBA – Loop Through Cells in Range

I was working on a report which gets exported from a Qlikview application.  To add some finishing touches, I needed to evaluate a range of cells, formatting the cell based on the cell value.  In cases where the range of cells was small, this could easily be done manually.  However, when you are dealing with thousands of rows, writing a simple VBA macro works well.

 

Setup:  Excel VBA – Loop Through Cells in Range

Here are the steps to accomplish this task:

For demonstration purposes, let’s say you have the following data:

Excel VBA - Loop Through Cells in Range

Change Cell Background Color

Now, let’s say you want to color the background for cells in column “D”, based on the value.  Setup a module and insert the following code:

Here is how the Excel Table looks after the macro runs:

Excel VBA - Loop Through Cells in Range

This simple code block provides an easy way to customize cell formats, as well as create new data within the Excel Table.

 

Build New Column

Here is a variation that will evaluate the contents of column H and build a new column.

Modify the macro code to the following:

Here is how the Excel Table looks after the macro runs:

Excel VBA - Loop Through Cells in Range

Again, a simple way to create additional content in a quick and easy code block.  You can customize this code to do many other tasks.

 

Dynamically Select Range of Cells to Loop

If you want to dynamically select the actual range of cells, due to varying number of rows, you can add the following code to accomplish this task:

If you run this updated block of code, here is how your Excel Table looks (I inserted a blank row after row 11 to illustrate):

Excel VBA - Loop Through Cells in Range

Conclusions

By using this technique, I am now able to customize the formatting of cells within a specified range.  This code block is especially useful when you are dealing with a large number of rows.  If you have any variations you would like to share, please add them to the comments on this post.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment