Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

If Then Statement in VBA

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

Contact Form

Name

Email *

Message *

Popular Posts