Find a number in a list.

[Matt5353]

I have a list of numbers 0-8200. I can use the find function on the home tab but I wish to have a search box that will highlight the number selected but you have to scroll through the list to find it. Is there a way I can have a search box on my spreadsheet so when I type in the required number it will go straight to the number without scrolling through the long list of numbers?
Andrew

 

[Matt5353]

It may be because you are using an older version of Excel than I am.
Try replacing the "xlFormulas2" in that search section with "xlFormulas", and see if that makes any difference.

If it does not, then please do the following:
1. Turn on your Macro Recorder
2. Record yourself hitting CTRL+F on your sheet to bring up the "Find" box, enter the value you are looking for, and hit "Find Next".
3. Stop your Macro Recorder
4. Go into VBA, and copy and paste the code you just recorded here so I can see what it looks like.

Its works now?
But when you press another number the highlight cell colour of yellow does not disappear in the cell you originally were in.
Will try to do what you asked

 

[Joe4]

If you do not want the cell permanently colored, then I would not change the color in this VBA code.
I would recommend removing these two rows:

'       Highlight range
        rng.Interior.Color = 65535

Then, I would recommend incorporate some other VBA code that dynamically highlights whatever the active cell on the worksheet is.
There are tutorials on how to set that up, like this one here:

 

[Matt5353]

Its works now?
But when you press another number the highlight cell colour of yellow does not disappear in the cell you originally were in.
Will try to do what you asked

Cannot seem to get this to work. I can switch on my record macro, but the find box I cannot get to work or come up.

 

[Matt5353]

Ok will try that I posted after you posted this did not see until I posted

 

[Joe4]

Cannot seem to get this to work. I can switch on my record macro, but the find box I cannot get to work or come up.

I am confused.

First you said it wasn't working.
Then you said "it works now".
So is it working or isn't it?

Any code that you record will probably be put in "Module1" in VBA. So you would need to open that module to see the code.

Alternatively, if you go back to your Macro menus and select "View Macros", it will show a list of all your macros.
The one you just recorded is probably called something like "Macro1" unless you chose to rename it.
Simply select it from the list and think click the Edit button.

If you do that, it will jump right to the code you edited, and then you can caopy and paste it here.

 

[Matt5353]

Ok will try that I posted after you posted this did not see until I posted

That works great
I just did some conditional formatting to highlight the active cell and that works on top of your code.
Thanks so Much
I owe you a beer.
Andrew

 

[Joe4]

You are welcome.
I am glad that it all worked out the way you want.