Today I'm going to show you how you can use
the IF statement in VBA. This is one of the most common statements,
one of the most useful statements in VBA because it comes in really handy whenever you want
to build in some type of logical thinking inside your macros. The IF VBA statement is actually very similar
to Excel's IF formula. the way it works is that you tell VBA what
you want to happen if a certain condition occurs, and you can have many conditions. You could have some conditions that have to
be true, that have to apply together, or some conditions where you want something to happen
if either of those conditions apply, so this is where you can work with the AND as well
as the OR functions in VBA. So let's start off simple. What I'm going to show you first is how the
IF function works on its own, and we're going to base it on one single cell. Once we get that to work, we're going to apply
an IF condition to a range of cells. So basically, we're going to loop through
them. First, let's bring up the VBA editor, Alt-F11. I'm going to insert a new module. Just click anywhere, right mouse click, Insert,
Module. By default it's module one, you can change
the name down here. The Sub: Let me just call this Simple_if. In the first Simple_if statement that we're
going to do, I want to copy this number in B9 to C9 if it's greater than zero. Okay, so all I have to do is type in "If" my
number is in range B9.Value, if that's greater than zero, then I want to copy it to C9, so
I'm going to use the assignment statement here and I'm going to say Range("C9").Value
= Range("B9").Value. You see I used IF here, and THEN. Now if you have everything in one line because
you just have one thing you want to occur after you typed in IF, then you can leave
it like this. You don't need to close the statement with
an End If, but the moment you have more things happening, so let's say we wanted to change
the color of this one, wanted to add a border to this, then we need to break it up like
this and then type in an End If on the bottom here. Let's try this, I'm going to press play. We're going to see 45 in here. Now if I put this to -2, let's just remove
this. Let's bring up the editor. Actually, I'm going to snap this is, I'm just
going to drag it all the way to the side to fix it. This way, it doesn't disappear all the time. Press play here. Nothing happened. If I put 500 here, and I press play, I get
500 here. How do we bring AND in here? We do it on the first line, so right here
I'm going to add an AND. What's the second condition that I want to
happen before I copy this value? Well, let's say I want the value to be between
1 and 400, so I'm going to make sure that Range("B9").Value is less than or equal to
400 before I do that. Okay, so when I press play, this number shouldn't
appear here. It doesn't. Now if I have 399 and I press play, it appears,
for 400, it should appear too. The OR function works in the same way like
the AND, except it checks if either of these conditions apply, and then it copies the
data. Let's now expand on this and loop through
this range and look for these different conditions. So let's do Sub IF_Loop. That takes us to task number two. Here, we want to highlight cells between 1
to 400 in green. This means we need to loop through this, which
means that we need to find the beginning of the range and end of the range, and there
are many different ways you can do this. You could fix the range, you could dynamically
find the end of the range by using xlDown or going the other way by going to the bottom
of your workbook and doing xlUp. Or looking for the current region. There are many ways and they cover them inside
my VBA course. I'm just going to show you one practical
way here, and that's to use table referencing. I'm going to convert this data set to a table,
so just click anywhere inside and press Control+T. The table has headers, so I'm going to say
OK. The first thing I do is I go and clear the
style, so it takes me back to my own style, and under Design, which is a tab that pops
up there because this is a table. I'm just going to change the name to Table
Sales and press enter. Now I'm gonna use this range here, so if I
just go to any cell and type in equals, and I highlight this just to get the syntax that
I need, I'm going to copy and paste this in there. Now in the cell, it tells me a value because
obviously, I have a lot of numbers here, it can't show them in one cell, but the moment
you put this inside a formula that can handle the different values, you're going to see
the numbers. But I don't care about this actually all I
want is this syntax, which I copied, I'm going to remove this and go here. Now I want the macro to look through each
cell in this range. I have a video on the FOR EACH statement. I'm going to link to that below this video
in the description. First, I'm going to dim my variables. I'm going to call this cell as a range. Now, I'm going to use my FOR EACH statements. For each cell in my range that I copied, so
that's going to be range - quotation mark, I'm going to paste what I copied, close this with Next
cell. Now our task says "highlight cells between
1 to 400 in green" Here, we need our IF statement, because we
would need to check the value of each cell. The value that we're checking is actually
what we did right here, so I'm going to copy and paste this right here. Instead of fixing the range to this one specific
cell, I'm going to do cell.Value, and for this one as well, cell.Value. So when you see it in red, or you get some
pop up message saying that it's invalid, that means that there's something missing. What is missing? Then. So If you have an IF, you need a THEN. I'm going to close the End IF. END IF is like
closing your brackets when you type in the formula. Now what do we want to happen? We want the cell color to be green. So that's cell - dot Now the color property belongs to the interior
object, and that's something that I also cover in my course in detail about how to find these
relationships. You can see that clearly in the object library
of VBA as well. So cell.interior.Color =, we can dig into
the library and get VBA's constant for green, and we can get to the library by typing in
VBA. It's in section ColorConstants., then we see
the colors. There is our vbGreen. But these colors aren't really that fancy,
so let's just take a look at how green works. I'm just going to press play and you can see
this. It's a bit too bright. Now what I normally do is I pick my own color. So let's say for green, I want this one. This is a much nicer green. To find the color code for this, what you
can do is use the immediate window. So I'm going to go to View, bring up the immediate
window, and I'm going to query the color of that. So notice that my cell is activated. Then I'm going to go to the immediate window,
put a question mark, type in activecell.Interior.Color. Press enter, that's the code I need to get
this green. So actually, I'm gonna change that to this
nicer green. So let's just run that. We get the nicer one. Okay, so that's a simple IF THEN statement. Now let's do something more. Let's make another exception. We want 1 to 400 to be green, but everything
else should be yellow. So let's bring up a nice yellow in here that
we want. So I'm going to go with this one. Now all I'm going to do is press enter to
get the code, which is this. How do I bring that in my IF statement? I use the Else. You're probably familiar with Excel's IF formula,
whenever you start writing, "IF something then this, otherwise this. Else is the otherwise. All you have to do is type in Else, and tell
it what you want to happen if your first condition doesn't happen, and I want this color to happen. So I'm going to press play, and everything
else is yellow. Let's make this a little bit more complicated
by adding in another condition. I want any negative values, I have one here,
to be red. Everything else yellow, what we had before
should be green. So 1 to 400: green. Negative: red. Everything
else: yellow. Let's come up with a nice red color. Let's go with this one. Now the cell is active, so I still have my
statement there. I'm going to press enter. That's the color code I can use. Where do I bring it in? I have IF and Else. So before the ELSE, I can add an ELSEIF statement,
and right after that I need to put this statement for what it should check for. So I wanna say if cell.Value is less than
zero. If it's negative, then I want the color to
be this 192, and notice the elseif statement also needs the THEN, so don't forget the THEN,
we're going to add in cell.Interior.Color = 192 here. Okay, so now we have an IF, ELSEIF. Basically, if the number isn't between this,
but it's minus do this. Otherwise for everything else, do this. So this means that this one should become
red when I press play, and it does. Okay, so that's how you can use the IF statement. That's how you can loop through a bunch of
cells, and that's how you can use the color code that you want to use in your files, in
your VBA statements. If you like this video and you want to learn
more about VBA, make sure you check out the complete course. That way you can learn it in a structured
way. And if you haven't subscribed to this channel,
you like what you see, you want to improve your Excel knowledge, do consider subscribing.
