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!

 

[Russk68]

It's the latter.
I have 12 columns A>L by 500 rows and any of those cells will have numbers in them in the format type I explained earlier. I want to enter a number anywhere in column M (e.g. 301 in M77) and if that number is found anywhere in A1:L500 it will result in a True conditional format.
I'm really amazed to see it work in a single row and if you can get it to work as explained above; I would be very thankful!

 

[Eric W]

OK, try this:

Change the code to:

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

    MyData = MyRange.Value
    For r = 1 To UBound(MyData)
        wk1 = Split(MyData(r, 1), "/")
        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 = r + MyRange.Row - 1
                Exit Function
            End If
        Next i
    Next r
    
End Function

Just to provide a bit more information, I changed it so that the function returns the first row where the match was found, instead of TRUE/FALSE. Not found = 0. The nice thing about this is that Conditional Formatting considers 0=FALSE, and non-zero=TRUE.

	A	B	L	M	N	O
101>123
13/15/19/24
101>123/125/128/15>20/25
177>190/195

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]TRUE[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N1[/TH]
[TD="align: left"]=checkrange($A$1:$A$500,M1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1[/TH]
[TD="align: left"]=IF(checkrange($A$1:$A$500,M1),TRUE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



As you can see, if you put the formula in column N, you'll see the list of matching row numbers. To use it with Conditional Formatting, just select column M and use the formula in N1. (If you use the formula on the sheet and want to see TRUE/FALSE instead of a number, either use the variant formula in O1, or we can tweak the UDF a little.)

Let me know how this works!

 

[Russk68]

That looks really cool! I'll get back to you.
Than you soooo much!

 

[Russk68]

Hi Eric
This is so close!
I tweaked your formula:=checkrange($A$1:$b$500,M1) and =IF(checkrange($A$1:$B$500,M1),TRUE) to search in column B as well but returns false when I enter a number in column B that matches a number in column M.
In your example above; if I type 101>123 in B1; N1=0 and 01=False.
Should my tweak to your formula, extend the range to include column B?

 

[Eric W]

Well, you never said you wanted a 2-D range!

Try this version of the UDF:

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

    MyData = MyRange.Value
    For r = 1 To UBound(MyData)
        For c = 1 To UBound(MyData, 2)
            wk1 = Split(MyData(r, c), "/")
            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 = r + MyRange.Row - 1
                    Exit Function
                End If
            Next i
        Next c
    Next r
    
End Function

Now the formulas you wrote should work fine.

If you really want more flexibility, you can use this version:

Public Function CheckRange2(MyRange, MyValue) As Long
Dim wk1 As Variant, wk2 As Variant, i As Long, lb As Long, ub As Long
Dim c As Range

    For Each c In MyRange
        wk1 = Split(c.Value, "/")
        For i = 0 To UBound(wk1)
            wk2 = Split(wk1(i), ">")
            lb = wk2(0)
            ub = wk2(UBound(wk2))
            If MyValue >= lb And MyValue <= ub Then
                CheckRange2 = c.Row
                Exit Function
            End If
        Next i
    Next c
    
End Function

This allows you to use disjoint ranges, like:

=checkrange2(($A$1:$A$500,$B$1:$B$20,$F$3,$J$10),M1)


It probably will run a bit slower, but I don't know if you'd notice.

 

[Russk68]

Hi Eric,
I posted yesterday but I must not have hit the reply button. I was having an issue with formulas in A:L returning 0 values that were hidden. I wrote the formulas to return "" and now it works GREAT!
I'm really amazed that this would even be possible to do!
Thank you VERY much for all your help!
Russ

 

[Eric W]

I'm glad it works for you!
Thanks for the feedback.

 

[Russk68]

Hate to ask, but can this macro only run on a specific sheet and only when changes are made in a specific column?

 

[Eric W]

I'm puzzled. This is a UDF. Yes, I can adapt it so that it runs as an event handler on a given sheet and column. It would check the value of the changed cell, and highlight it if it fulfills the conditions. However, that would be functionally identical to just selecting that column and using the UDF as the CF rule. What exactly different do you want?

 

[Russk68]

My VBA skills are very weak so I'm probably not even asking the right question. This works great on Windows but when I tried it on Mac, the following things become sluggish: Scrolling down the sheet; selecting cells and typing in the formula bar, and sometimes excel just closes. When I remove the module, everything works fine. I was thinking that if the macro just ran when changes were made in the 1 column that it might possibly fix the issue but I'm guessing it's more than that. Unfortunately, the machines that I would like your awesome code to run on are Macs. I can't expect you to dig into it, but I'm hoping that it might be something simple that causing this.
Thank you again!