Find a number within a range

[ayazgreat]

Sir
I want to ask subject question , all I wanted to do is ask that can it be possible, we can find a number within a range through user form.

In a workbook ,having 4 or 5 sheets , diffrent ranges are scattered with their start range and end range like mentined below
Start Range End Range
2100100 2100199
2130100 2130199

If I want to search number like 2100125, then a user form finds it and goes to that cell containing searched number

 

[ayazgreat]

If Jindon is not here who will answer it?

 

[jdc]

Hi

Correct me if I am not understanding this, what you need is the ranges conditionally formatted based on a number (which could be already in a cell or from a userform input).

Assuming you have a number in cell D1.

Conditionally format cell A2 to a formula which is =IF(AND($D$1>$A2,$D$1<$B2),1,0)
Conditionally format cell B2 to a formula which is =IF(AND($D$1>$A2,$D$1<$B2),1,0)

to a border colour of your choice.

Then using the Format painter copy the formatting down to include all the ranges.

If the value in D1 changes the range will instantly show up.

Hope this helps

John

 

[ayazgreat]

Thanks jdc but this will not work in the case of number between range

 

[ayazgreat]

Jindon is not here could anyone answer it please?
<!-- / message --><!-- sig -->

 

[ayazgreat]

Jindon is not here could anyone answer it please?
<!-- / message --><!-- sig -->
<!-- / message --><!-- sig -->

 

[jdc]

What version of excel are you running and have you got the macro security set to allow the action. I have tested it on several other pc's running excel 2003.

regards

John

 

[ayazgreat]

Could anyone please solve it ?

I want to ask something more, if mentioned below codes can be change to Sheet even codes instead of using input box, codes search mywhat in same x and y.

Like when I enter numbers in below range and if number found within a range then both cell highlighted as mentoned below example.

Temp

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 86px"><COL style="WIDTH: 104px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Start Range</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">End Range</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21006</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21010</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21015</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">21406</TD><TD style="TEXT-ALIGN: right">21412</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">81000</TD><TD style="TEXT-ALIGN: right">81006</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81023</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">80300</TD><TD style="TEXT-ALIGN: right">80301</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">80804</TD><TD style="TEXT-ALIGN: right">80812</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">80304</TD><TD style="TEXT-ALIGN: right">80311</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81025</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

Sub test()
Dim myWhat, x, y, myRow As Long
myWhat = Application.InputBox("What to search?", Type:=1)
If myWhat = False Then Exit Sub
With Sheets("sheet1")
    .Select
    x = Application.Match(myWhat, .Range("a:a"), 0)
    y = Application.Match(myWhat, .Range("b:b"), 0)
    If Not IsError(x) Then
        myRow = x
    ElseIf Not IsError(y) Then
        myRow = y
    End If
    If myRow = 0 Then
        x = Application.Match(myWhat, .Range("a:a"), 1)
        y = Application.Match(myWhat, .Range("b:b"), 1)
        If Not IsError(x) Then
            If Not IsError(y) Then
                If x <> y Then
                    myRow = x
                Else
                    MsgBox "No such data"
                End If
            Else
                myRow = x
            End If
         Else
             MsgBox "No such data"
         End If
    End If
    If myRow > 0 Then .Range("a" & myRow).Resize(, 2).Interior.Color = 37
End With
End Sub

 

[HarrisM]

This might do it for you. I've only briefly tested it and it'll need a little tidying.

It may take a while if you have a lot of ranges. If more than one range is found it should ask if you want to go to the next one.

Sub test()
Dim EV As Long
EV = Application.InputBox("Enter Number", Type:=1)
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
        For Each cell2 In ActiveSheet.UsedRange
            If cell2.Value = "" Then
            End If
        ActiveCell.Interior.ColorIndex = 2
        Next
    For Each cell In ActiveSheet.UsedRange
        If cell.Value = "" Then
        Else

            ' For Excel 2003
            ' If cell.Column = 256 Or cell.Row = 65536 Then
            ' For Excel 2007
            If cell.Column = 16384 Or cell.Row = 1048576 Then
            Else
            LLimit = cell.Value
            ULimit = cell.Offset(0, 1).Value
                If EV >= LLimit And EV <= ULimit Then
                    Application.ScreenUpdating = True
                    cell.Activate
                    ActiveCell.Interior.ColorIndex = 6
                    ActiveCell.Offset(0, 1).Interior.ColorIndex = 6
                    res = MsgBox("Range Found! Click 'Yes' to go to next found range or 'No' to stop here", vbYesNo)
                    Application.ScreenUpdating = False
                        If res = vbYes Then
                        Else
                            Application.ScreenUpdating = True
                            Exit Sub
                        End If
                Else
                    RFound = 0
                End If
            End If
        End If
    Next
Next
If RFound = 0 Then MsgBox "No (or no more) ranges found"
Application.ScreenUpdating = True
End Sub

 

[ayazgreat]

HarrisM

Thanks for your reply, I think that you did not read my question carefully please read it again red highlighted

I want to ask something more, if mentioned below codes can be change to Sheet even codes instead of using input box, codes search mywhat in same x and y.

Like when I enter numbers in below range and if number found within a range then both cell highlighted as mentoned below example.

Temp

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 86px"><COL style="WIDTH: 104px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Start Range</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">End Range</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21006</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21010</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21015</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">21406</TD><TD style="TEXT-ALIGN: right">21412</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">81000</TD><TD style="TEXT-ALIGN: right">81006</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81023</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">80300</TD><TD style="TEXT-ALIGN: right">80301</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">80804</TD><TD style="TEXT-ALIGN: right">80812</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">80304</TD><TD style="TEXT-ALIGN: right">80311</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81025</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR></TBODY></TABLE>

 

[jdc]

Hi

I think you are going to continue having your questioned unanswered, I know its a pain, can you totally reword your question, I don't understand what you mean by "Sheet even codes".

Without entering any code just explain what it is you are expecting to happen.

regards

John