h2eribbonmod

How to Customize the Ribbon in Excel Using XML

If you want to customize the ribbon in Excel then you know simply changing doing it through the front end is only going to work on your computer. The customization isn’t technically saved within the file and it won’t move from one file to another unless you actually adjust the xml.

It’s by no means an easy process, but if you’re just looking to add a custom tab with some buttons for some macros that you have, I can show you a quick way to do that as painlessly as possible. It’s by no means comprehensive, but it’ll get the job done.

Step 1: Open your Excel file in a program like Winzip/7-Zip

If you right-click on your Excel file you should have an option that says Open Archive if you have a program like Winzip or 7-Zip installed. There, you should see something that looks like this:

These are the files and folders that are within the Excel file itself.

Step 2: Create a folder called customUI

Step 3: Open up notepad and paste the following into it:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group id="myMacros" label="My Macros">
<button id="Macro1" label="Macro1" imageMso="HappyFace" size="large" onAction="cbMacro1" />
<button id="Macro2" label="Macro2" imageMso="TableDrawTable" size="large" onAction="cbMacro2" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

What the code will do is add two buttons to the end of the Home tab. I’ll show you how to modify it further down this post.

Save the file as customUI.xml

Step 4: Put the file into the customUI folder that you created in Step 2

Step 5: Back in the archive, navigate to the _rels folder and open the .rels file (in Notepad)

Find this line:
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

and then insert the following right after it:

<Relationship Id="R4863ef4e23f1404c" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

Step 6: Close the archive, save changes and open the file

You should now see the two buttons at the end of the home tab:

The problem is that they don’t do anything just yet. In the code that you copied back in Step 3, there were some callback items (‘CB’) that we need to reference back to inside VBA. Those are effectively a link from when a user presses the button on the ribbon to the macro that you’ve coded.

What you’ll need to do now is go into a module within VBA and enter the following code:

Sub cbMacro1(control As IRibbonControl)
***name of your macro***
End Sub

Sub cbMacro2(control As IRibbonControl)
***name of your macro***
End Sub

In the subprocedures for the two buttons all you need to do is call your macro (in place of the code within the ***), and now the buttons should work.

Modifying the code to add more customization

In Step 3, the code there was for a couple of buttons that you could add to your ribbon. If you want to add more, simply following the sequence you can easily add another button:

<button id="Macro3" label="Macro3" imageMso="Smile" size="large" onAction="cbMacro3" />

The button id doesn’t matter too much itself. The label is what will show up underneath the button. ImageMso is the image that will show up. If you do a google search for ImageMso you’ll find what some of the different codes are.

You can shrink the image down to “small” by changing the size attribute here. Note that some of the ImageMso’s are already small and can’t be made large, but you can shrink large ones down in size.

The most important item when adding a button is the onAction attribute as this is what your callback code needs to reference inside VBA. Obviously the more consistently you name your buttons the easier it’ll be to add more without getting lost in your code.

Adding a new tab

In the above code, I added the buttons to the Home tab. However, if you’ve got more that you want to add then you can put them on an entirely new tab instead.

What you can do then is just change the following:

<tab idMso="TabHome">

into this:

<tab id="MyMacros" label="My Macros">

And now all of your macros will be saved onto a new tab rather than take up space on the Home tab. You can also group your buttons based on the group code as well.

This is a small sample of what you can do to customize the ribbon in Excel. The benefits of making these changes through XML and not within Excel’s interface is that your modifications are stored within the file regardless of what computer you open it on and won’t be lost.

There is another way that you can do this using the CustomUI editor and I’ll include that in a later post.

matrix-3408055_640

HR Tip: 3 Ways to Tell Whether Someone Knows VBA or Not

If you’re looking to hire someone and want to know whether they know how to code in Excel using Visual Basic (VBA), it’s not too hard of a task to quickly evaluate whether they know it or not. The first step is to ask them to send you a sample of something that they’ve done that includes coding and that is not password protected.

Then you’ll want to test to see what it does. So you’ll want to ask how it works so you can see for yourself. If that code works and the macro does what it’s supposed to do, you might be thinking that will be enough. However, someone could simply use a macro recorder to try and generate the code. This is not the same as coding and anyone can do this with no knowledge of code whatsoever. But there’s an easy way to uncover this.

Finding the code

In the file that someone’s sent you, hit ALT+F11. This will send you into Excel’s backend and open up VBA. You should see something like this on the left-hand side:

Double click on each of those items – sheets, workbook, and any modules. Code can reside in any and all of those areas so you might need to cycle through to see where it is.

Once you find the code, that’s when you can start evaluating it.

Reviewing the code

Below, I’ll show you the same macro, how it might look in VBA compared to how it looks using the macro recorder:

 

coding using VBA

 

using the macro recorder

There are three things that should be clear from comparing the two examples above, which will help to identify whether someone’s just using the macro recorder or whether they’re actually coding properly using VBA.

1. Organization and spacing.

The macro recorder doesn’t care for spacing out the code and each line of code will come after the other. Especially when you’re looking at longer lines of code, it’ll get real messy real quick. Organization is important because if it looks like one big block of text it’s going to make it very difficult to audit or review later should you want to make changes.

2. No comments.

In the first example, there were lines in green that started with an apostrophe, called comments. They are optional but it ties back to the organization and putting notes along the way to help remind you what you were trying to do. It doesn’t have to accompany every line, but if you don’t see any comments at all, it could be a hint that someone just used a recorder. For a quick macro that’s only a couple lines long it probably wouldn’t be necessary, but for a lot of code you would certainly expect to see at least some comments.

3. .Select.

In the second example, you’ll notice .select showing up multiples times. This makes it obvious that someone’s used the macro recorder. If you want to insert a column or bold it, you can just code it right away, you wouldn’t need to actually select it and then make the change. The macro recorder, however, records everything, including those selections. So seeing this should tell you right away that someone’s just used a recorder rather than coding it themselves.

There are other ways you could see whether the macro recorder was used or not but these three should suffice in helping you identify whether someone knows how to code or not.

Why does this matter?

If someone knows how to use the macro recorder, that’s good, but it’s not knowing how to code. The problem is that the macro recorder could do a small fraction of what is possible through actual coding. Coding through VBA opens up a lot more opportunities for automation and improving a spreadsheet. A macro recorder can be used by anyone but it lacks the sophistication to build much logic into it.

meetinga2

Action Items Template – How to Stay Productive

Excel can be a useful tool for tracking items, whether related to a meeting or a project. The action items template allows you to enter multiple fields related to an action item, including responsible person, department, expected completion, and tags to help organize them.

You’ll be able to create new action items, sort them into different department tabs, recall the ones you want when it’s a new meeting, update the items, and archive them when they’re done.

Let’s start from the beginning.

First, fill out all the fields relating to the action items from your meeting.

action items template

The tags field will help when you recall meeting items in case you only want ones related to sales, a project, or some other criteria. Tags can be separated any way you want – comma, space, or any other separator.

Then, click Save New Items, which will put them into every relevant tab.

action items buttons

By default, I have the sales and marketing tabs setup, but if you need more departments simply copy those tabs. If a tab doesn’t exist for the department, then you’ll get an error and it won’t be able to populate those tabs.

However, you don’t need to have a department for each action item and can simply assign a generic one.

The sales tab now shows the action item:

action items

If a comment is left blank, then it will simply say ‘no update’ was made. In the comment field, it will always show the date of the meeting.

Now, say you want to make a new meeting and want to populate the action items. Click on the New Meeting button. This will give you the opportunity to enter any tags:

action items tags

You can enter up to three different tags in your criteria. This is where if you have a specific type of meeting you can use the tags to help identify which items you want to populate in your meeting list. Of course, this assumes you entered the tag in the action item to begin with. If you do want to include everything, leave the tags blank and just click Done

When you pull up a new meeting it will only recall the most recent comment. Any items that show the completion at 100% will not populate the meeting items.

Any comments you enter now in the Latest Update field will add to the existing comments.

If you have items that are completed and don’t want to see them on the individual department tabs, you can click on the Archive Items button and that will move the items into the Archive tab.

Download the Action Items Template

Download link

Like this template? Give us a like on Facebook and be sure to check out our other templates here

addina

Free Excel Add-In: 20+ Macros to Automate Tasks and Make You More Efficient

 
 
 
 

This add-in is completely free and includes over 20 macros that I have worked on myself and that I hope will help you. Any feedback is welcome, as well as any suggestions for other macros you would like to see added.

Disclaimer
These macros have not been tested exhaustively so I don’t offer any guarantees that they will work under every possible scenario. However, if you run into any issues please let me know and I will work to correct them. When using macros you should always save your work before executing them, as there is no undo button if something doesn’t go as expected.

If you understand and accept these risks, please feel free to download the file here

Below is an overview of all the different macros in the file.

Toggling Workbook and Worksheet Calculations

For those that work on large spreadsheets, this can make it easy for you to not only turn off and on calculations for a workbook, but for individual worksheets. It will also allow you to see whether or not they are set to on or off.

One of the things people sometimes don’t realize is if you turn off calculations in Excel at the workbook level, that disables it for all other workbooks. The danger is if you switch to another file you’re working on you may not realize calculations are still off, by seeing the toggle and whether it is set to on or off can help prevent that.

If you only need an individual worksheet to be off, you can do that as well. However, note that if the workbook calculations are set to off, then all the worksheet calculations will be off as well, regardless of whether or not they say on or off. Workbook settings will supersede any worksheet settings.

Very Hidden Tabs

 

Hiding tabs in Excel may seem pointless since even an average user would know that you can right-click and select un-hide. However, not many know that you can set them to be ‘very hidden’ and where right-clicking won’t do anything.

I’ve covered this in a post before here, and in this add-in I’ve made it so that you can easily both hide and unhide very hidden tabs.

 

Removing Excess Spaces

 

This macro will delete any trailing, leading, or extra spaces in a cell and will help to clean up your data.

Converting Formulas to Values

In some instances you may want to get rid of your formulas and replace them with their results (values), this macro will do that for you. Just select the cells and click the button and the formulas will be gone.

Converting Numbers to/from Text and Changing Signs

These buttons will allow you to choose whether you want to convert numbers that are stored as text into numbers, switch numbers back into text, or just flip the signs from positive to negative or vice versa.

Filtering Out Zero Values From Tables

 

If you’ve got a table or pivot table that has a lot of zero values in it that you don’t want to see, this will filter them out. This won’t get rid of errors, just zeros, and for it to work in a table, it assumes that the table will start in column A, otherwise it won’t filter the right column for you. The zero values will be removed from the column where your active cell is, so you have to make sure you’ve got the right cell selected before clicking this button.


















Multiplying and Dividing by a Factor of 1,000

This is pretty straightforward and is mainly here since dividing can be helpful if you’re dealing with financials and want to cut down the number of placeholders. Multiplying will simply undo those changes.

Combining Columns

If you have data across multiple columns and want to combine it, you can do that with this macro. You don’t have to select entire columns, it can just be a selection. The columns don’t even have to be right next to one another.

 

Cycling Through Errors

 

If you want to find all the errors on the worksheet you’re on, this macro will cycle through all of them for you. You can correct an error, and click the Next button to go onto the next error in the sheet.


Removing Merged Cells

When you’re trying to do data analysis, merged cells can be a nightmare, and this will unmerge the cells and put the value into each of the cells as well.

 

Protecting Your Data


This will help convert your sensitive data into a random number preceded by a series of X’s. There’s a post here detailing how that process works.

 

Filtering Pivot Tables

If you’ve got a pivot table and want to select multiple items, it can be a tedious process. This macro will allow you to select what selections you want to filter by and apply them for you. But the first cell in the selection needs to match the field name in the pivot table.

 

Adjusting the Default Pivot Table Format

One of the more annoying things in Excel is that when you create a pivot table, it defaults to a format that isn’t very useful. This is what the macro will help you do:

 

Quickly Formatting Pivot Table Fields

If you’ve ever needed to change how fields are formatted in a pivot table you know that simply selecting the column and changing the format is a temporary fix. You need to actually go into the field settings. This macro will do that for you, and will set the settings to either comma or accounting format.

Quickly Extracting Unique Values

 

There are plenty of ways you can get unique values, but I thought an even easier way would be to select the cells and specify where you want those unique values to be output.

Counting Unique Values

If you just want to quickly count how many unique values are in your selection, this macro will do that for you.

Do a Reverse Lookup

Everyone knows how to do a VLOOKUP, but doing the reverse is another story. Take for example a credit card statement. You could have a lot of detail in the string, but only a certain few characters relate to the actual vendor or detail you want:

Using this lookup function the cells you select will be compared against a list you have specified, and if there are any matches, the corresponding field will be returned:

 

The result:



If you’re doing this with a lot of cells and have a big list, it could be time consuming, and that’s why I added a progress bar to this macro.

Comparing Sheets

This macro essentially looks at two sheets and tells you what is different, and will highlight the differences in them.

 

Updating Links

If you want to update the link for a cell, it’s not an easy process and involves you right-clicking on the cell and putting the link in there. This macro will do that step, and for the link it will put the cell’s value there, so if you put in the url you want in the cell and then run the macro on those cells, the links will be updated.

Adding the Location to the Footer

Clicking this button will add the path to the workbook you’re working on into the footer so when you
print it out it’s easy to see where the file is saved.

Calculating and Tracking Streaks with a Custom Function

With the stocks markets tanking earlier this month, I thought it’d be interesting to track their historical performance and put into perspective just how badly things have been going lately. For those that don’t know, one of my side jobs is writing articles for the Motley Fool Canada and so naturally this example attracted my interest.

However, there’s not an easy way to calculate this in Excel, and so I decided to go the route of a custom function.

What I’m going to be looking to accomplish is a way to to track how many consecutive trading days that a stock has been up or down, and then also calculate the cumulative value of those gains and losses.

If you’d like to follow along with my example, you can download the file I used here (you’ll have to save the file, open it in Excel and enable content, otherwise you’ll see NAME? errors)

Setting Up the Variables


I want the calculation to start from the bottom (the current cell) and work its way back up, since the latest results will be at the bottom. To do this I create a ‘bottom’ variable that looks like this:

————————————————————————————————————–

bottom = selection.Count + selection.Row – 1

————————————————————————————————————–

I want the user to be able to select what range they want the calculation to apply to, rather than selecting everything.

I also setup a variable for the column, which I named as offsetnum:

————————————————————————————————————–

offsetnum = selection.Column

————————————————————————————————————–

These two variables allow me to set my starting point for my calculation.

Determining if I’m Counting Negatives or Positives

The value of the starting cell will determine if I am going to be looking for positive numbers (gains) or negatives (losses), and so I setup an if statement to determine whether the first value is a gain or loss:

————————————————————————————————————–

If Cells(bottom, offsetnum) < 0 Then
    posneg = “negative”
Else
    posneg = “positive”
End If

————————————————————————————————————–

Start counting


The final step involves counting the values depending on whether I’m looking for positives or negatives:

————————————————————————————————————–

For counter = bottom To 1 Step -1

    If posneg = “negative” Then
 
            If Cells(counter, offsetnum) < 0 Then
                streak = streak – 1
            Else
                Exit For
            End If
         
      Else
   
            If Cells(counter, offsetnum) >= 0 Then
                streak = streak + 1
            Else
                Exit For
            End If
         
        End If
     

Next counter

————————————————————————————————————–

My complete function looks as follows:

————————————————————————————————————–

Function streak(selection As Range)

Application.Volatile
Application.Calculate

Dim bottom, offsetnum As Integer
Dim posneg As String

bottom = selection.Count + selection.Row – 1
offsetnum = selection.Column

‘Determine first value
If Cells(bottom, offsetnum) < 0 Then
    posneg = “negative”
Else
    posneg = “positive”
End If

For counter = bottom To 1 Step -1

    If posneg = “negative” Then
 
            If Cells(counter, offsetnum) < 0 Then
                streak = streak – 1
            Else
                Exit For
            End If
         
      Else
   
            If Cells(counter, offsetnum) >= 0 Then
                streak = streak + 1
            Else
                Exit For
            End If
         
        End If
     

Next counter

End Function

————————————————————————————————————–

Calculating consecutive points gains and losses


Now that I have a function to tell me the current winning or losing streak, I can calculate the cumulative gains and losses.
To do this, I am going to sum as far as the streak goes. And so far starters, I’m going to start with the SUM function. I am also going to use the OFFSET function because I need to determine how many rows up I need to add. The OFFSET will start from the current position and determine how far back I need to go to add up the totals in the current streak.

However, because some streaks are negative, I’ll need to also use the ABS function to just grab the number, regardless of if it is positive or negative. My formula looks like this so far:

=SUM(OFFSET(H2,1-ABS(I2), 0

Column H is where my gain or loss value is, while column I is the streak value. Since I want to sum the cumulative gains, I need to reference column H as my starting point.

I added the 1- before the ABS function because that will ensure the number is a negative, meaning that my formula will calculate upward, rather than downward if the number were positive. I also have to decrease the number of cells to offset because I don’t want to include the current cell, otherwise the formula will go too far.

Since I’m not offsetting any columns I set the next argument to 0.

The last argument I need to enter is the height of the offset function, otherwise the formula will just offset by the number specified in the second argument and pull that value, rather than pulling all the values that fall within the range.

This actually involves just copying the same argument again, but this time for the height. My completed formula looks as follows:

=SUM(OFFSET(H2,1-ABS(I2),0,ABS(I2)))

Note

Sometimes with custom functions you might notice that your calculations hang or stop computing correctly. What that means is you just need to recalculate using either F9 or you can edit in the cell and click enter, which will normally trigger a recalculation as well.

Alternatively what you could do is after running the formulas for the dataset, copy them over as values to ensure that they don’t change, since in this case you likely wouldn’t need to recalculate the streak value again.

savecharts3.png

Save a Chart as an Image File

Excel has a lot of different charts that you can use to summarize your data with. If you want to use your chart in PowerPoint or Word it’s an easy copy and paste job, but suppose you wanted to save the chart as a .gif, .png, or .jpeg file? Then you would need the help of VBA to accomplish that.

The code below will save the chart that you’ve selected as a .jpeg file into same folder as your Excel file:

____________________________________________________________________

Sub SaveChartAsJPEG()

Dim Fname As String
If ActiveChart Is Nothing Then Exit Sub
Fname = ThisWorkbook.Path & “” & ActiveChart.Name & “.jpeg
ActiveChart.Export Filename:=Fname, FilterName:=”jpeg
End Sub

____________________________________________________________________

The code above will save the file as a jpeg, but you can change it to .png, .gif, or whatever format you prefer by just changing the values in red.

Note: If your chart is small then your image will be as well. If you want the chart to save as a large image you’ll want to stretch it out first and then run the macro.

Once you’ve saved the code then what you’ll probably want to do is assign a shortcut key for the macro so that you can easily save whichever chart you’ve selected.

You can read this post on how to insert code into VBA. It will also show you how you can assign a shortcut key to a macro.

Hide Tabs Using VBA to Ensure Users Cannot Unhide Them

When you’re creating a template in Excel for other users often times there is information in the backend that you’d prefer users not be able to access or modify. In some cases it might be preferable to just hide the data entirely, especially if it contains sensitive information.
The easiest way to hide a tab in Excel is simply to right click on a tab and click hide.
The problem with this approach is as easy as it is to hide you can unhide it as well, which many users know how to do, and just involves right-clicking on a tab and clicking Unhide
After that you can see all the sheets that are hidden and by select the sheet and clicking OK you can unhide it.
You could protect your spreadsheet and prevent users from changing the structure but that might not be preferable either as it involves password protection and will not allow users to insert, copy, or even rename worksheets. If they don’t need this functionality then the solution might work for you.
To protect your workbook simply select the Review tab and click on the Protect Workbook button and you’ll see a pop up where you can protect the structure. Make sure to just tick off Structure and enter a password and click OK.
Another way to hide tabs is through one line of VBA code. If I wanted to hide Sheet1 I could use the following code:
Worksheets(“Sheet1”).Visible = xlVeryHidden
If your sheet is named something else then you would just change Sheet1 to the name of the sheet you want to hide.
Now when I go back to my spreadsheet and right click unhide:
Unhide isn’t even an option because there are no tabs that can be unhidden. If I hid another tab without using VBA then only that one would be visible, but the one using the code I used above would not show.
If you want to unhide the tab, the code to unhide is as follows:
Worksheets(“Sheet1”).Visible = True
What you could do is have two different procedures, one to hide a tab and another to unhide it. Then you can assign a shortcut key to each procedure. This will easily allow you to hide and unhide any tabs that you want to be invisible.

If you are not familiar with VBA check out one of my first posts on how to insert code and assign shortcut keys to a macro. 
AR

Accounts Receivable Statement – Template Using VBA

Download Template

 
 
accounts receivable statement

This is an updated version of a prior post that generated a statement without VBA, but the limitation there was you had to save the pdf yourself. This updated version allows you to do the following:

– Save the current statement to PDF
– Cycle through all your customers and save all their statements to PDF
– Generate an email (but not send) to the customer with the attached statement.

It is important to note the customer name on the invoice data needs to match the name on the customer data tab otherwise the invoices will not pull on the statement correctly.

The template works in exactly the same way as the prior version – invoice data and customer data needs to be downloaded from your accounting software. You can customize your statement and include any images you like so that it will look consistent for every statement that you send out.

This template is setup to accommodate up to 150 invoices.

Passing Variables in VBA Using ByRef and ByVal

In many cases it is inefficient to run one long sub procedure in VBA. Rather, it is easier to break them up into smaller processes. I’m going to show an example of how to do this using a simple square root function, and having a variable passed from another sub procedure.
My initial subprocedure will just assign a test value, use the square root function to determine the square root, and then return a message box to summarize the results.
————————————————————————————
Sub test()
Dim testvalue as Integer, root as Integer
testvalue = 25
root = sqrt(testvalue)
Msgbox “the square root of ” & testvalue & ” is ” & root
End sub
————————————————————————————
The root variable comes from the sqrt function which is below:
————————————————————————————
Function sqrt(cl as Integer) as Integer
sqrt = cl ^ 0.5
End Function
————————————————————————————
The sqrt function takes the cl to the power of 1/2; its square root.  The sqrt function requires a variable for it to work, which is indicated by the parentheses, indicating that it requires the cl variable to be an integer. That is the key requirement, that the variable passed to the sqrt function is the same data type.
The sub procedure at the top assigns 25 to the testvalue variable.  The next line calls the sqrt function and uses the testvalue variable as the argument for the function. Effectively it is going to calculate the square root of 25. This result will be assigned to the root variable. The last line before the end of the sub procedure simply pops up a message box to say what the square root of the testvalue is.
If I run the first sub procedure this is what the message box results in:
As you see the square root of 25 is indeed 5, so the calculation worked correctly.
However, let’s pretend my function has an additional line where it sets the cl variable to 9:
————————————————————————————
Function sqrt(cl as Integer) as Integer
sqrt = cl ^ 0.5
cl = 9
End Function
————————————————————————————
 Now when I run the sub procedure the message box I get is this:
Obviously this is not correct. And you’ll notice that now my variable in my sub procedure has been changed to 9. The reason for this is because the variable has been passed to the function by Reference, or ByRef. As a result, when the sub procedure sends the testvalue variable to the sqrt function, the testvalue variable effectively assumes the cl variable, and when the cl variable is changed to 9, when the variable is now sent back to the sub procedure it is with the value of 9. Sending a variable by reference allows for the variable to be changed by the function. 
The other way to pass a variable is to pass it by Value, or ByVal. Becuase ByRef is the default in VBA, it does  not have to be stated. But to pass it ByVal that needs to be specified. Below is the adjusted function now taking only the value rather than the reference:
————————————————————————————
Function sqrt(ByVal cl as Integer) as Integer
sqrt = cl ^ 0.5
cl = 9
End Function
 ————————————————————————————

 Now when I run the sub procedure I get the following message box:
The calculation reverts back to the correct message I had initially before I added that line of code to change the cl variable. But even with the change in code, by setting the variable to ByVal, it ensures my original variable cannot be changed in the sqrt function. 

Doing Loops in VBA to Highlight Cells and Delete Rows

Doing loops in VBA is very useful because it allows you to go through a range of cells, evaluate each, and then take an action if a specified criteria is met. I am going to go through a couple of examples to show you how you could do this to highlight cells and also delete rows.

For a brief intro to macros check out my earlier post about how to access VBA and record macros.
I have a data set below that shows population by country in the year 2015:
First, what I will do is do a loop to evaluate each country’s population for 2015, and for any that had more than 1,000,000 people, highlight the country name in column A.
There are a number of ways to do loops in VBA but I prefer using the For…Each loop. 
The key thing is first to define the range of cells I want the loop to be applied to. I can use Excel’s COUNTA function to count the range. To do so, I first setup a variable called totalcount:

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
WorksheetFunction allows me to use Excel functions that are accessible in a normal spreadsheet and are accessible without VBA, in this instance, COUNTA. The totalcount variable now tells me  how big my range needs to be; to what row I need my loop to go to.
I created a couple of additional variables as well: cl – this is the specific cell that will be evaluated when the loop is in progress; ws – this is just the worksheet which allows me to shortcut having to spell out the name of the worksheet each time (in this case it is just the default Sheet1). Below are my variable declarations:
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
The first line in my loop specifies the range that I am going to cycle through:
For Each cl In ws.Range(“A2:A” & totalcount)
The above can be interpreted as saying that I am going to cycle through each cell from the range A2 until A and whatever the totalcount is equal to. I have to close the loop as well, so the last line of the loop is as follows:
Next cl
The point of the above line is that once I finish evaluating the cell and making whatever changes I need I now move onto the next cell to evaluate.  This variable does not have to be called cl, it just makes it easier to think of instead of next a or b let’s say.
So that is the loop. But without anything inbetween the start and end of the loop all the sub procedure is going to do is go through every cell in that range and do nothing. So as mentioned I want to determine if the population is more than 1,000,000:
    If cl.Offset(0, 1) > 1000000 Then
I use the offset function to evaluate the column adjacent to colum A. I could have selected my range as being B rather than A and I wouldn’t need to do an offset. However, using offset(0,1) tells the procedure to look at the column to the right. Just like the regular excel function, the first argument in the offset function is the number of row offsets, and the second is the column offset. So the above line evaluates if the related value in column B is more than 1,000,000. The next line I enter is what will happen if it is true:
        cl.Interior.Color = vbYellow
This will highlight the cell in yellow. There are a lot of things you can do and in VBA once you type the period after your range, in this case once I enter cl. it will show me a list of properties and methods that I can use. It makes it easy if you don’t remember the exact property or method name:
If I wanted to make the cell bold I would do the following: 
cl.Font.Bold = true
I did not use the offset function here as I want the country field in column A to be highlighted and bolded. My entire sub procedure from start to finish looks as follows:
—————————————————————————————————————–
Sub highlight()
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
For Each cl In ws.Range(“A2:A” & totalcount)
    If cl.Offset(0, 1) > 1000000 Then
    
        cl.Interior.Color = vbYellow
        cl.Font.Bold = True
        
    End If
Next cl
End Sub
—————————————————————————————————————–
After running the above subprocedure this is what my table now looks like:
I can also use a counter to accomplish the same thing, in which case I don’t use the cl variable
—————————————————————————————————————–
Sub highlight2()
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
counter = 0
For counter = 1 To totalcount
    If ws.Range(“B” & counter) > 1000000 Then
    
        ws.Range(“A” & counter).Interior.Color = vbYellow
        ws.Range(“A” & counter).Font.Bold = True
        
    End If
Next counter
End Sub
—————————————————————————————————————–
The above code will work in the exact same way as the previous, the only difference is the counter. You’ll see in the below examples why a counter is useful.
Suppose I wanted to delete the rows where the population was less than 1,000,000 people in 2015. I could modify my original code to work as follows:
—————————————————————————————————————–
Sub deleterow()
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
For Each cl In ws.Range(“A2:A” & totalcount)
    If cl.Offset(0, 1) < 1000000 Then
    
        cl.EntireRow.Delete
              
    End If
Next cl
End Sub
—————————————————————————————————————–
The key difference is instead of bolding and highlighting I have the following line:
cl.EntireRow.Delete
This line will delete the row that the cell is on. If I run this subprocedure, I get the following result:
You’ll notice there is a problem. I have a country that has less than 1,000,000 people in this list – Antigua and Barbuda. It worked in a few instances – it got rid of Aruba, Andorra, and American Samoa. The problem is because American Samoa was right before Antigua and Barbuda. When I delete a row the loop does not factor in that the row that Antigua and Barbuda was on has now moved up a slot so when the loop goes to the next row it has skipped over the one that Antigua and Barbuda is on. So the loop fails in this instance when you run into consecutive rows meeting the condition you have specified.
However all you need to do to correct this is to start your loop backwards: start from the last row and work your way up. To do this I use the counter, The opening line of my loop is as follows:
For counter = totalcount To 2 Step -1
If you recall totalcount is my last row, and it will go until it reaches row 2. Step -1 indicates that it should go up one row at a time (if I wanted it go up two rows at a time it would be Step -2 instead). That way if I delete a row it doesn’t affect my loop since the data above the row I’ve deleted remains the same. My new sub procedure looks like this:
—————————————————————————————————————–
Sub deleterow()
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
counter = 0
For counter = totalcount To 2 Step -1
    If ws.Range(“B” & counter) < 1000000 Then
    
        
        ws.Range(“A” & counter).EntireRow.Delete
                
    End If
Next counter
End Sub
—————————————————————————————————————–
Now when I run the above sub procedure I get the following result:
Now you’ll notice Antigua and Barbuda is now gone and my list only contains countries with populations of more than 1,000,000 people.