Add multi-select ListBox if intersection contains string

I have previously achieved code to insert a multi-select ListBox onto my worksheet based on selection.

I would like to add further conditions to add the ListBox only if Column 7 is selected and Column 2 in the same row has the string "variable".

Pseudo code:

If ActiveCell.Column = 7 and if ActiveCell intersection with Column 2 contains the string "variable" then

Add multi-select ListBox below active cell and 

Output selections to ActiveCell.

Here is part of the code I am using currently using. It works to add a ListBox to every row in a specified column, rather than to a qualifying row in the specified column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, ColourArea(ActiveSheet)) Is Nothing Then
        CreateColourPopUp Target
    Else
        DeleteAllPopUps Target
    End If
End Sub

How can I add the extra conditions mentioned above?

Solution

The Application.Intersect method Returns a Range object that represents the rectangular intersection of two or more ranges.

I wouldn't approach what you want to do using this function.

Instead I would use the Target parameter of the Worksheet_SelectionChange event with all my If...Then logic.

Something like so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub 'Makes sure no more than 1 cell is selected.
    If Not Intersect(Target, ColourArea(ActiveSheet)) Is Nothing Then 'Not sure if this is required or not but remove this If...Then block if it's not needed.
        If Target.Column = 7 And Target.Offset(0, -5).Value = "Variable" Then
            CreateColourPopUp Target
        End If
    Else 
        DeleteAllPopUps Target
End Sub

This adds the extra If...Then block to check the following conditions are True before creating the listbox's;

Target.Column = 7 checks the selected cell is in column 7.
Target.Offset(0, -5).Value = "Variable" checks the value of the cell 5 columns to the left of the selected cell is "Variable".

For the syntax of the Offset property, see the documentation here.