Search Box

M

megsyp

New Member
Joined
Jul 8, 2014
Messages
6
I have a search box via conditional formatting in a spreadsheet of 4 columns and x amount of rows, that when data is entered it highlights the results. How can I simply get the results to show and not the rest of the data. The spreadsheet is meant for a user who needs to be able to type in the search box as I know he would not use the Find button on the ribbon!!.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
You need to right click on the textbox and find out what it is called in the properties. It's probably called Textbox1, but just verify it. That is how we will call it in the code. You didn't specify which column it is searching through. I'm going to assume in my code that it will search through column A. If it finds a match in a row, then it will unhide. If it doesn't match, it will hide.
Code: 
Sub myMacro()
    mySearch = LCase(TextBox1.Text)
    ColSearch = "A"
    i = 1  'i = 2 if you have headers
    lastRow = Range(ColSearch & Rows.Count).End(xlUp).Row
    Do Until i > lastRow
        rowValue = LCase(Range(ColSearch & i).Value)
        If rowValue LIKE "*" & mySearch & "*" Then
        'Or if you want exact match then.
        'If rowValue = mySearch Then
            Rows(i).Hidden = FALSE
        Else
            Rows(i).Hidden = TRUE
        End If
        i = i + 1
    Loop
End Sub
 
Upvote 0
thank you for replying. I think I have not explained properly. (new to this) I have a created a search box in my spread sheet simply by naming the cell search_box and conditionally formatted it to highlight the results in a colour.The range it will look in is 4 columns and any amount of rows(new details are added regularly). as my rows could be many I want only the the rows that satisfy the text in the search box to show and not the whole spread sheet. How do I add the criteria to do this?
 
Upvote 0
Code: 
mySearch = Range("A1").Value
replace the A1 with the cell you are using as your search box. use the rest of the code i already gave you
 
Upvote 0
thank you again for your help but i have not used vba or macro. my search box is a cell with conditional formatting specifying a range across 5 columns and x amount of rows. when data is entered in the cell it highlights any row that may have the data entered somewhere in that row. i just want to show only the highlighted rows. I could of course sort the results by cell colour but I would like to find a way of doing it all in one action- probably not possible without VBA or macro!!
 
Upvote 0
I'm not going to help until you post your dataset to this discussion. Preferably 1 table with your dataset and a seperate table of what it should look like after the macro is run.
 
Last edited:
Upvote 0
SEARCH
NAME
VEHICLE
REG
TEL 1
MARY JONES
MAZDA DEMIO
MAR12V
CORSA
CK56207
VAUXHALL ZAFIRA
CN52408
DALE WORTH
SUZUKI SX4
CN60112
VAUXHALL ASTRA VXR
BF54AJK
MEGAN PATON
MEGANE
VW GOLF
FRED
FORD KA
BD36DEF
077985682
CORSA
BB45456
077985683
BETTY
FORD FIESTA
BB45457
077985684
JOE
SKODA FABIA
BB45458
077985685
JOHN
BERLINGO
BB45459
077985686
JILL
LEXUS RX300
BB45460
077985687
MARTHA
MISUBISHI COLT
BB45461
077985688
MARY BROWN
RENAULT LAGUNA
BB45462
077985689
FRED
CIRROEN C4
BB45463
077985690









<tbody>
</tbody>
 
Last edited:
Upvote 0
after search entered
SEARCHford
NAMEVEHICLEREGTEL 1
MARY JONESMAZDA DEMIOMAR12V
CORSACK56207
VAUXHALL ZAFIRACN52408
DALE WORTHSUZUKI SX4CN60112
VAUXHALL ASTRA VXRBF54AJK
MEGAN PATONMEGANE
VW GOLF
FREDFORD KABD36DEF077985682
CORSABB45456077985683
BETTYFORD FIESTABB45457077985684
JOESKODA FABIABB45458077985685
JOHNBERLINGOBB45459077985686
JILLLEXUS RX300BB45460077985687
MARTHAMISUBISHI COLTBB45461077985688
MARY BROWNRENAULT LAGUNABB45462077985689
FREDCIRROEN C4BB45463077985690
NEIL TAYLORSUZUKI WAGON RBB45464077985691
DARREN BROWNCITROEN PICASSOCK61567077985692
CHEVROLET MATIZCK61568077985693
PEUGEOT 206CK61569077985694
JEFFRENAULT SCENICCK61570077985695
PEUGEOT 206CK61571077985696
AUDI S LINECK61572077985697
FORD FIESTACK61573077985698
SEATCK61574077985699
DARREN GREENMERCEDESCK61575077985700
FIESTACK61576077985701
KACK61577077985702
MEGANECK61578077985703
FIATCK61579077985704
KIA CARENSCK61580077985705
PEUGEOT 206CK61581077985706
CITROEN XSARA PICASSOCK61582077985707
FORDCK61583077985708
MAZDACK61584077985709
FORD FIESTACK61585077985710
FORD FOCUSCK61586077985711
PEUGEOT 207CK61587077985712
FORD FIESTACK61588077985713
KIA VENGACK61589077985714
MAZDACK61590077985715
CITROEN C2CK61591077985716
VW LUPOCK61592077985717
VW LUPOCK61593077985718
FORD ESCORTCK61594077985719
MEGANECK61595077985720
FIESTACK61596077985721
FORD KACK61597077985722
VW POLOCK61598077985723
FORD FIESTACK61599077985724
CORSACK61600077985725
INSIGNIACK61601077985726
PEUGEOT 207CK61602077985727
SUZUKI SWIFTCK61603077985728
VAUXHALL ZAFIRACK61604077985729
ASTRACK61605077985730
RENAULT CLIOCK61606077985731
AUDI TTCK61607077985732
HYUNDAICK61608077985733

<colgroup><col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 128pt; mso-width-source: userset; mso-width-alt: 6253;" width="171"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>
 
Upvote 0
The second data set highlights the complete row where "ford" could be in any cell- for some reason when I pasted it lost the highlight in those rows but it does work perfectly.
 
Upvote 0
megsyp said:
The second data set highlights the complete row where "ford" could be in any cell-
Click to expand...
.. so could you just use Conditional Formatting like this?

Select from top left cell of data (A5 for me) to Dxx where xx can be as far down the sheet as you like and apply the Conditional Formatting shown.
The "5" value in the formula needs to be whatever row the active cell is in for the range you selected to apply the formatting to.

Excel Workbook
ABCD
1SEARCHford
2
3
4NAMEVEHICLEREGTEL 1
5MARY JONESMAZDA DEMIOMAR12V
6CORSACK56207
7VAUXHALL ZAFIRACN52408
8DALE WORTHSUZUKI SX4CN60112
9
10VAUXHALL ASTRA VXRBF54AJK
11MEGAN PATONMEGANE
12VW GOLF
13FREDFORD KABD36DEF77985682
14CORSABB4545677985683
15BETTYFORD FIESTABB4545777985684
16JOESKODA FABIABB4545877985685
17JOHNBERLINGOBB4545977985686
18JILLLEXUS RX300BB4546077985687
19MARTHAMISUBISHI COLTBB4546177985688
20MARY BROWNRENAULT LAGUNABB4546277985689
21FREDCIRROEN C4BB4546377985690
22
Conditional Format
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A51. / Formula is =SEARCH($B$1,$B5)Abc
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
229
Akuini
Akuini
E
  • Question
Search Boxes for users
Replies
2
Views
342
Eurekaonide
E
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
194
still learning
S
Y
  • Question
Combo Boxes in User Form Keep repeating the options as I clear Them with Command Button
Replies
9
Views
108
dmt32
D
J
  • Solved
VBA 3 Column Table and Display each column separately in 3 list boxes.
Replies
2
Views
60
JulianvO
J

Forum statistics

Threads
1,215,805
Messages
6,126,998
Members
449,351
Latest member
Sylvine

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top