Macro to search number with in a series.

[Fernando]

Dear frineds,

I have an Excel table like below,


A B C
1 Box Number Seal num. from Seal num. to

2 BOX 1 004569 004600

3 BOX2 020034 020106

4 BOX3 000237 000320

5 BOX4 001003 001130



I want to write a macro to search a Seal Number with in the series. For example,
When I input 020100, it should highlight the row 3. as well when I input 000300
It should highlight row 4.

Could anyone give me a bright idea as how to do this?
Thank you in advance.

 

[Tom Ogilvy]

Assume you want to react with a value is entered in G2

right click on the sheet module and select view code. Paste in code like
this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
If Target.Address = "$G$2" Then
Cells.Interior.ColorIndex = xlNone
Set rng = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
If cell.Value <= Target And cell.Offset(0, 1).Value >= Target.Value Then
cell.EntireRow.Interior.ColorIndex = 6
Exit Sub
End If
Next
End If
End Sub

Assumes you aren't using colored cells on the sheet, except for this
highlight.

 

[Priyanga Fernando]

Thank you very much Mr Tom Ogilvy it works perfect.

Insted of column B and C if I have the "seal series" in colum M and O
How should I alter this code?

Is it also possible to select the cell S on the highlighted row?

I really appriciate your help.

 

[Priyanga Fernando]

Sorry I forgot to mention.
There can be occurrences in this nature too,

A B C
1 Box Number Seal num. from Seal num. to

2 BOX 1 004569 004600

3 BOX2 102000 103000

4 BOX3 102100 102500

5 BOX4 102200 102300



In this situation if you input 102250 to search, three rows have to be
highlighted. Row 3, 4, and 5.
And the selected cell should be at the first occurrence on the row J

After I saw your solution Mr. Tom Ogilvy , only it strikes me about
these new problems.

Sorry for the trouble.

 

[Tom Ogilvy]

You said select S, then in the next post you say something about J. The
below selects S. Change to J if that is what you want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range, cell as Range, bFound as Boolean
If Target.Count > 1 Then Exit Sub
If Target.Address = "$G$2" Then
Cells.Interior.ColorIndex = xlNone
Set rng = Range(Cells(2, 18, Cells(Rows.Count, 18).End(xlUp))
bFound = False
For Each cell In rng
If cell.Value <= Target And cell.Offset(0, 1).Value >= Target.Value Then
if not bFound then
cells(cell.row,"S").Select
bFound = True
End If
cell.EntireRow.Interior.ColorIndex = 6
End If
Next
End If
End Sub

 

[Priyanga Fernando]

Hello Mr Ogilvy,

It works perfect.

Now I sort of figured out
Set rng = Range(Cells(2, 18), Cells(Rows.Count, 18).End(xlUp))
By changing the values of this line you can select the cells that Seals
numbers are. I have no idea what

Cells(Rows.Count, 18).End(xlUp) and
If cell.Value <= Target And cell.Offset(0, 1).Value >= Target.Value
does.

My VB knowledge is minimal. Will I trouble you, if I sak you to comment
your coding? So that I could learn something from it. At least above two
lines.

Thank you very much again.

Best regards,

Fernando