Matching cell values to two defined column ranges

[Beard]

Hello.

I have been trying to figure this out, but I have failed.

I have two ranges, A6:A29 and C5:C29.

In a group of cells that are not adjacent, if a number is entered in any of those cells, I would like to compare that number with the list of numbers I have in the two ranges.

If a match is found a popup box will appear with some text.

I have looked at VLOOKUP and MATCH, but I do not understand how to use the functions.

It is probably quite simple, but I cannot figure it out.

Thank you in advance.

 

[jim may]

Here's a good start.. Paste the below into your Sheets Code module

Excel 2012
	A	B	C	D	E	F	G
1						403
2
3
4
5			429
6	317		277
7	448		163
8	113		396
9	150		440
10	492		117
11	220		337
12	150		143
13	190		403
14	174		394
15	170		421
16	372		344
17	425		335
18	454		213
19	408		330
20	419		496
21	433		198
22	369		489
23	430		114
24	463		313
25	404		347
26	384		440
27	444		330
28	288		311
29	202		314
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRng As Range, Found As Range
If Target.Cells.Count > 1 Then Exit Sub
Set TargetRng = Range("F1:G3")   'Change this range to suit your needs
If Not Application.Intersect(Target, TargetRng) Is Nothing Then
With Range("A6:A29", "C5:C29")
    Set Found = .Find(What:=Target.Value)
    If Not Found Is Nothing Then
        MsgBox "Located number in Cell " & Found.Address
    Else
        MsgBox "Number not found"
    End If
End With
End If
End Sub

 

[Beard]

Here's a good start.. Paste the below into your Sheets Code module

Excel 2012

	A	B	C	D	E	F	G
1						403
2
3
4
5			429
6	317		277
7	448		163
8	113		396
9	150		440
10	492		117
11	220		337
12	150		143
13	190		403
14	174		394
15	170		421
16	372		344
17	425		335
18	454		213
19	408		330
20	419		496
21	433		198
22	369		489
23	430		114
24	463		313
25	404		347
26	384		440
27	444		330
28	288		311
29	202		314

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRng As Range, Found As Range
If Target.Cells.Count > 1 Then Exit Sub
Set TargetRng = Range("F1:G3")   'Change this range to suit your needs
If Not Application.Intersect(Target, TargetRng) Is Nothing Then
With Range("A6:A29", "C5:C29")
    Set Found = .Find(What:=Target.Value)
    If Not Found Is Nothing Then
        MsgBox "Located number in Cell " & Found.Address
    Else
        MsgBox "Number not found"
    End If
End With
End If
End Sub

This has been very helpful. Thank you.

 

[jim may]

Glad it helped you. Thanks for letting us know (it was helpful).