in sheet search query to search sheet for a given cell.

D

Dave1155

I am setting up a spreadsheet to be used as a floor plan for a call center.
the floor plan has over 500 stations and I am trying to create a query witin
the spreadsheet that will allow a user to enter a station number and it will
display the location on the floor plan. I do not want the user to be able to
use the find function on the tool bars...I just want a cell that a location
can be entered and then the location will be highlighted on the sheet.
Any thoughts would be appreciated.
 
D

Debra Dalgleish

You can use conditional formatting to highlight the station.

For example, if the user types the station number in cell D1, and the
stations are numbered, in cells A3:O61 --

Select cells A3:O61
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =AND($D$1<>"",A3=$D$1)
Click the Format button
 
B

bigwheel

Hi Dave1155

Would this be of any use?

Dim workstation
workstation = InputBox("Search for what?")
Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Cells.Find(What:=workstation, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Place a button on the worksheet with the caption "Find Workstation" and
assign the above macro to it. When your user clicks the button the macro
will present an input box and then search on whatever is entered. (no error
checking has been included in the code)
 
D

Dave1155

Thanks for the quick reply...once i read it, I gave myself a slap in the back
of the head...I have used conditional formatting for other drop down
selections, but did not consider here in this case....thanks again
 
D

Dave1155

Thanks for the quick reply...I like the idea of the macro, can be changed to
suite other needs as required.
 
Top