search for a value in a range

[RoseGuy]

Is there a way to search for a value that's in between a range of numbers and have it find the correct cell. For instance,

I want to be able to search for 51477 and have it highlight Column A4.

 

[6StringJazzer]

I think you want

=MATCH(51477, A:A, 1) + 1

That will find the largest number less than or equal to 51477, then return the next number.

What do you mean by "search" and "highlight"? Do you mean you want conditional formatting?

 

[RoseGuy]

I want it to locate the cell in column A if possible. I give out thousand of sequentially numbered tags grouped in quantities of 100 listed in column A and B. When people bring unused tags back, I have to find the correct row and then I type in the number of the first unused (in Column C) but there are so many rows it’s hard to scroll quickly and find the correct row.

 

[6StringJazzer]

This formula can be adapted to conditional formatting to highlight the cell with a fill color, but VBA could actually put your cursor in that cell so you don't have to scroll looking for it:

Public Sub findit()

   Cells(Application.WorksheetFunction.Match(Range("C1"), Range("A:A"), 1) + 1, "C").Select

End Sub

 

[RoseGuy]

Thank you very much. That works for what I need.

 

[6StringJazzer]

You're welcome, glad to help.

 

[RoseGuy]

VBA code makes a cell active after user input in cell C2. Works great. I would like to now copy that input into the cell where the cursor is. That would be simple except the input and the active cell changes with every user input. Is there code to say whatever cell is active, copy and paste the current user input from C2?

 

[6StringJazzer]

Is there code to say whatever cell is active, copy and paste the current user input from C2?

ActiveCell = Range("C2").Value

 

[RoseGuy]

You rock! Thanks!

 

[smozgur]

Thank you very much. That works for what I need.

@RoseGuy: Welcome to the MrExcel Message Board, and great to see you got the solution to your question!

Could you please mark the solution post that answers your question? Therefore, future visitors would know this question has a working solution.