Find a number in a range of a cell

[Russk68]

Hello all,

I have this # range in cell A1 101>123.
I'm using the > symbol as a thru statement.
My question is; if I put 105 in B1; can a formula or VB return a true by looking at A1 and matching 105.

I would like conditional formatting in B1 to turn green if 105 is in the range of numbers in A1

I also would need to search this range as well; 13/15/19/24

Thank you!

 

[JLGWhiz]

Select cell B1, open your conditional formating dialog box and click New Rule. Then click 'Use a formula to determine whcich cells to format'. Put this formula in the 'Format values where this formula is true' box

=FIND(A1.B1,1)

. Then click the 'Format' button and select 'Fill' to set your fill color. Click OK to exit out of the dialog box and cell B1 should now fill with your selected color when you enter a number in the range you have in cell A1.

 

[Aladin Akyurek]

Hello all,

I have this # range in cell A1 101>123.
I'm using the > symbol as a thru statement.
My question is; if I put 105 in B1; can a formula or VB return a true by looking at A1 and matching 105.

I would like conditional formatting in B1 to turn green if 105 is in the range of numbers in A1

I also would need to search this range as well; 13/15/19/24

Thank you!

In CF involving B1 invoke the following formula:

=MATCH(B1,ROW(INDIRECT(SUBSTITUTE(A1,">",":"))),0)

Format the cell as green.

By the way, search

13/15/19/24

for what?​

 

[JLGWhiz]

By the way, search

13/15/19/24

for what?​

@Aladin Akyurek , what I read was that the OP would enter a value in cell B1 to look for.

 

[Russk68]

Hi, can't wait to try it!
Sorry. If A1 contained 13/15/19/24 or even more #s and if B1 contained 19, then B1 conditional format would be true.

 

[Russk68]

That works great, but I just realized that I have to go one step further.

I need the search to extend from A1 to A1000. So A1 could be 101>123, A2 124>129, A3 130>146 and so on. So if B1= 133 it would be true.

Thank you!

 

[Eric W]

Aladin's formula will work fine for the 101>123 case, but when you have lots of individual numbers to check, you might be better off with a UDF. For example:

	A	B	C	D	E
101>123
13/15/19/24
101>123/125/128/15>20/25

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=checkrange($A1,B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



On a copy of your workbook, right click on the sheet tab on the bottom and select View Code. Then from the menu in the VBA editor click Insert > Module. On the sheet that opens paste this code:

Public Function CheckRange(MyRange, MyValue) As Boolean
Dim wk1 As Variant, wk2 As Variant, i As Long, lb As Long, ub As Long

    wk1 = Split(MyRange, "/")
    For i = 0 To UBound(wk1)
        wk2 = Split(wk1(i), ">")
        lb = wk2(0)
        ub = wk2(UBound(wk2))
        If MyValue >= lb And MyValue <= ub Then
            CheckRange = True
            Exit Function
        End If
    Next i
    CheckRange = False
    
End Function

Now close the editor (Alt-Q or the red X in the corner). Now you can use the formula as shown above. You can even use that same formula in Conditional Formatting if you want.

 

[Russk68]

Hi Eric
I will give it a go later today and let you know how it worked out.
Thank you very much!

 

[Russk68]

Hi Eric,
I'm very impressed how this works! But I was too focused on how Excel could possibly find a number in a range as I described and I didn't explain the whole picture. I would greatly appreciate it if you could further assist me with the proper explanation below:
These formats 101>190 or 101/112/114 or 101>190/101/112 can be in any cell A1:L500. Column M is where the number is entered and want to condition format if the number is found in A1:L500.
Hope that makes sense!
Thank you!

 

[Eric W]

Do you mean if the number in M4 is found in the range of A4, then highlight it? Or do you mean that if the number in M4 is found in A1, or A2, or A378, or anywhere in the list of ranges in A? If the former, then just select column M, click Conditional Formatting > New Rule > Use a formula > and enter =CheckRange($A1,M1) > and select your fill color. If the latter, then I'll either need to change the UDF, or possibly set up a Worksheet_Change event to monitor when you change column M.