Searching Value in ComboBox as per comboBox Columns

[SamDsouza]

Hello
I would like to search values of Combobox Column values
For eg typing J in comboBox column 1
it should dropdown the list with all J and respective range in column2
Now if i type A or D or : in column 2 dropdown list to display all the names and and range
if typed Specificaly the Range A36: D42 in col2 of combobox then DropDown to display Steve A36:D42

What to code inorder to run the below following properly
1. Search Value Column wise as explained above
2. Combobox to display the Searched Name and respective Range or Searching Range in col 2 to display its respective Name in Column 1
3. After Searching, Selecting the item, Clicking or Pressing Enter to get the values in respective textbox

The Following code when executed
1. Combobox takes the full List of Names and Ranges
2. When clicked on DropDown Arrow and selecting an item it displays the Name only the Range section disappears
3. When Pressed Enter with Names shown(only) in combo List it executes Combobox_Exit Event

Private Sub UserForm_Initialize()
   Dim Rng As Range
   
   For Each Rng In Sheet1.Range("B:B").SpecialCells(xlConstants).Areas
      With Me.ComboBox1
         .AddItem Rng(1).Value
         .List(.ListCount - 1, 1) = Rng.CurrentRegion.Address(0, 0)
      End With
   Next Rng
   Me.ComboBox1.ColumnCount = -1
   Me.ComboBox1.ColumnWidths = "50;50"
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Dim Fnd As Range
   Dim Rw As Long
   
   Set Fnd = Sheet1.Range("B:B").Find(me.ComboBox1.text, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      With Fnd.CurrentRegion
         If .Cells(.Rows.Count, 2) = "Balance :" Then Rw = .Rows.Count - 1 Else Rw = .Rows.Count
         Me.txtPymnt = .Cells(Rw, 3)
         Me.txtPymtRcd = .Cells(Rw, 4)
         Me.txtRngAdd = .Address(0, 0)
         Me.txtRngFrom = Split(.Address(0, 0), ":")(0)
         Me.txtRngTo = Split(.Address(0, 0), ":")(1)
         If .Cells(Rw + 1, 3) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 3)
            Me.lblBal.Caption = "Balance : To be Paid"
         ElseIf .Cells(Rw + 1, 4) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 4)
            Me.lblBal.Caption = "Balance : To be Recieved"
         Else
            Me.txtBalance = ""
            Me.lblBal.Caption = "Balance"
         End If
      End With
   End If
End Sub

	A	B	C	D
1	Individual Name:	Jerry
2	Transaction Date	Description	Payments Made	Payments Received
3	02-04-2015	vchr no : 2	600
4		sfdfdsf
5	03-04-2015	vchr no : 3	200
6		rerrt
7	04-04-2015	Agst vchr no : 2		600
8		rwewrew
9	Total		800	600
10	Balance :			200
11
12	Individual Name:	Mike
13	Transaction Date	Description	Payments Made	Payments Received
14	02-04-2015	vchr no : 4	1750
15		gfhfhgfhgf
16	03-04-2015	Agst vchr no : 4		350
17		oipoiopo
18	Total		1750	350
19	Balance :			1400
20
21	Individual Name:	Kate
22	Transaction Date	Description	Payments Made	Payments Received
23	01-04-2015	vchr no : 5	1500
24		dfgfgff
25	05-04-2015	vchr no : 6	600
26		fsfdsfds
27	06-04-2015	Agst vchr no : 12		2000
28		fdfds
29	09-04-2015	Agst vchr no : 5		1500
30		ggfgfgfg
31	10-04-2015	Agst vchr no : 2		300
32		fdfdsfds
33	Total		2100	3800
34	Balance :		1700
35
36	Individual Name:	Steve
37	Transaction Date	Description	Payments Made	Payments Received
38	02-04-2015	vchr no : 4	1235
39		dfdsfds
40	03-04-2015	Agst vchr no : 4		1235
41		fdfdsf
42	Total		1235	1235
43

<tbody>
</tbody>

SamD