View Single Post
 
Old 12-15-2018, 06:08 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

Right click the sheet's tab, View Code, and paste. Add the reference as commented.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range, c As Range, e
  'Tools > References > Microsoft Scripting Runtime
  Dim dic As Dictionary     'Early Binding metho
  
  Set c = Intersect(Target, [D4:G7])
  If c Is Nothing Then Exit Sub
  If c.Count <> 1 Then Exit Sub
  
  Set dic = New Dictionary
  For Each c In Range("K3", Range("K3").End(xlDown))
    If Cells(c.Row, "K") = Cells(Target.Row, "C") And _
      Cells(c.Row, "L") = Cells(3, Target.Column) Then
      e = Cells(c.Row, "J")
      If Not dic.Exists(e) Then dic.Add e, Nothing
      End If
  Next c
  
  If dic.Count = 0 Then
    MsgBox "None", , "Names Found"
    Else
    MsgBox Join(dic.Keys, vbLf), , "Names Found"
  End If
End Sub
Reply With Quote