Excel VBA Practice 2

 

Suppose we added another student to the spreadsheet from our previous lesson. We want to set a grade for a score of 35 or below. The grade will be an "F". However, because this is such a low score, we want to highlight the student's name, the score and the grade. We want to colour all three cells red, so we can quickly see that this student is failing and needs extra attention. When a button is clicked, we want to go from this:

A spreadsheet showing a student score in a cell

to this:

A spreadsheet showing a student's score highlighted in red

How can we do all this with Excel VBA?

The first thing to do is the grade. This is more or less the same as before. In fact, you can add an ElseIf part to the code you already have. We want to test for a score of 0 or greater to a score of 35 or lower. The ElseIf part would be this:

ElseIf score >= 0 And score <= 35 Then

The code to place a value of "F" in the cell to the right of the ActiveCell is also about the same as before:

ActiveCell(1, 2).Value = "F"

ActiveCell(1, 2).Interior.Color = RGB(255, 0, 0)

ActiveCell(1, 2).HorizontalAlignment = xlCenter

So add that code to your own SetGrades Sub and it will look like this:

Excel VBA code setting properties of some cells

This code colours the cell to the right of the ActiveCell. The only thing left to do is to colour the ActiveCell itself, and the cell to the left of the ActiveCell. This is slightly trickier.

If the ActiveCell is cell B2 then the range of cells we're trying to colour is cells A2 to B2:

Range("A2:B2")

Except, we don't want to specifically name the cells, otherwise we couldn't reuse the Sub further down, for student Jack, say, on row 3. So we need a way to refer to our two cells based on the ActiveCell. We can use Offset with ActiveCell.

ActiveCell.Offset(, -1)

Notice the comma between the round brackets of Offset. With Offset, you type the Row number first then the Column number. If you don't want to change the Row number then you can leave it blank. Type a comma, and then the column number you want to change. In the code above, we're pointing to 1 column to the left of the ActiveCell (-1). For us, this will get us the "A" column

To get to the B column, we'd need this:

ActiveCell.Offset(, 0))

We can then wrap all that up in the Range object:

Range(ActiveCell.Offset(, -1), ActiveCell.Offset(, 0))

The first ActiveCell code between the round brackets of Range is for the first cell of the range we want to highlight. After a comma, we have the second ActiveCell code. This refers to the end of the range we want to highlight.

Now that we have the correct range of cells, we can add the interior colour part:

Range(ActiveCell.Offset(, -1), ActiveCell.Offset(, 0)).Interior.Color = RGB(255, 0, 0)

This line is a lot longer and more complicated than any you've met before. So don't worry if you don't completely understand it at first glance. Try it out though, and get a feel for how it works.

Here's the complete code, though:

Complete VBA code for this exercise

 

In the next lesson, we'll take a look at another option you have when you need some Conditional Logic - Select Case.