The better alternative is to create a dynamic named range, "CountryList" for the country list using:
= OFFSET(Sheet1!$X$1,0,0,COUNTA(Sheet1!$X:$X))
Name the cell K9 "CountryPick"
Then your VB code would just be a sheet change event that checks if X9 changes and pops the msgbox:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$9" Then
Dim CityList As Range, _
CapCity As String
Set CityList = Sheets("sheet1").Range("countrylist").Resize(COLUMNSIZE:=2)
MsgBox "Capital is " & WorksheetFunction.VLookup(Range("countrypick"), CityList, 2, False)
End If
End Sub
Bookmarks