In this post I will show you how to use If then statement in VBA. So let’s develop simple If then statement for sample data below.
These are scores of few students. Assume we need to color the scores which are less than 40. So let’s learn how to do that step by step.
It is a good practice to define the variables.
|
Dim WS As Worksheet Dim i As Integer |
Next let’s assign activesheet to WS. Here we assume that user will be in the sheet where table is in when he run the macro.
| Set WS = ActiveSheet |
Next we need to use For loop as we have several rows. Actually this kind of programs are more Effective when we have thousands of rows.
|
For i = 3 To 9 Next i |
Inside the for loop, we need to check whether each value is less than 40 or not. This is the point where we need help of a If then statement. We can use a if then statement like below.
|
For i = 3 To 9 If WS.Range("C" & i).Value < 40 Then WS.Range("C" & i).Interior.Color = RGB(255, 0, 0) End If Next i |
Here we used RGB color system to color the cells. So once you run the program for above sample data you will get this result.
And if you want to color the cells in more lighter red color then you can use RGB values like RGB(255, 150, 150) , RGB(255, 200, 200) etc. Following is the result I got for RGB(255, 200, 200)
And here below is the complete subroutine.
|
Sub HighlightLowScores() Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 3 To 9 If WS.Range("C" & i).Value < 40 Then WS.Range("C" & i).Interior.Color = RGB(255, 0, 0) End If Next i End Sub |
