If Then Insert Cell

Intermediate Exercise

Statement

Make a loop in the worksheet (that contains country data) to identify the lines between which the first letter of the country name changes (e.g. from A of Azerbaijan to B of Bahamas) Whenever there is a change insert in between a blank cell with green background and white borders

Suggested Concepts:

Dim
Cells
End .Row
For Next
If Else
Left
With

Remember that in most cases there is more than one way to solve the exercise.

Hint: Use the If statement to compare the first letter of the names between each line

Solved Exercise

Exercise Solution

Start by declaring the variables and finding the last data entry in the worksheet. As one line is inserted for each letter, we add to the last line variable the total letters of the alphabet (26).
```
Dim LastRow As Long
Dim i As Integer

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastRow = LastRow + 26 'Plus the number of letters in the alphabet
```

Make a For Next loop with the conditional If in order to differentiate the first letter of the names between each line Subsequently, just add the additional line and fill it with the desired color.

For i = 18 To LastRow
    'Just to prevent a new line when the code runs more than once
    If Cells(i + 1, 2).Interior.Color = RGB(140, 198, 63) Then
        Exit Sub
    End If

    If Left(Cells(i, 1), 1) <> Left(Cells(i + 1, 1), 1) Then
        Range(Cells(i + 1, 1), Cells(i + 1, 3)).Insert shift:=xlDown

        With Range(Cells(i + 1, 1), Cells(i + 1, 3))
            .Interior.Color = RGB(140, 198, 63)
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(255, 255, 255)
            .Borders.Weight = xlMedium
        End With

    i = i + 1
    End If
Next i

Consolidated Answer

Sub Solution()

    Dim LastRow As Long
    Dim i As Integer

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = LastRow + 26 'Plus the number of letters in the alphabet

    For i = 18 To LastRow
        'Just to prevent a new line when the code runs more than once
        If Cells(i + 1, 2).Interior.Color = RGB(140, 198, 63) Then
            Exit Sub
        End If

        If Left(Cells(i, 1), 1) <> Left(Cells(i + 1, 1), 1) Then
            Range(Cells(i + 1, 1), Cells(i + 1, 3)).Insert shift:=xlDown

            With Range(Cells(i + 1, 1), Cells(i + 1, 3))
                .Interior.Color = RGB(140, 198, 63)
                .Borders.LineStyle = xlContinuous
                .Borders.Color = RGB(255, 255, 255)
                .Borders.Weight = xlMedium
            End With

        i = i + 1
        End If
    Next i

End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED