Hey there, I'm going to walk you through one
approach to loop through a selected range of
data using a macro in Excel VBA.
Here we have budget EBITDA financial data
for a list of business units.
Our macro will bolden numbers greater than
zero, or italicize values
less than or equal to zero in the loop.
Open "Visual Basic for Applications"
and insert a module.
Remember to place a description in your macro.
Let's call the macro, "loopSelection"
Disable the following three Excel properties;
using the Application object, as this will improve
the speed and efficiency of our looping macro.
It is good practice to get into the habit of
notating every step of your macro,
as this will help you or others
to understand your macro at a later date.
Next, we want to declare the object variables,
which we will use to compose the macro.
We will use the Set keyword to specify
our selected range as the range object variable.
We are now ready to compose our loop
of the selected range.
The "For Each cell In rng" syntax is the critical
piece of code, which will enable us to loop
through our selected range of financial data.
To demonstrate this looping macro
as mentioned above, we are inserting a simple
"If" statement, which will bolden cell values that
are greater than zero, or else italicize the value
if equal to or less than zero.
Alternatively, you can insert some other code
inside the For Each code, if you want the
looping macro to do something else with your selected data.
Once the macro has run, we will move the
cursor to the first cell in the selected data range.
We will then re-enable those three Excel properties,
which we disabled or altered at the top of our code.
We will now review our macro,
return to our data set and then run the macro
on our selected data range.
As we can see, our looping macro is correctly running.
It has looped through selected data,
and formatted it according to our requirements.