Search Box

[megsyp]

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!!.

 

[WarPigl3t]

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.

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

 

[megsyp]

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?

 

[WarPigl3t]

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

 

[megsyp]

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!!

 

[WarPigl3t]

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.

 

[megsyp]

[TABLE="width: 312"]
<tbody>[TR]
[TD="width: 90, bgcolor: transparent"]SEARCH
[/TD]
[TD="class: xl70, width: 171, bgcolor: #FFC000"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NAME
[/TD]
[TD="class: xl65, bgcolor: transparent"]VEHICLE
[/TD]
[TD="class: xl66, bgcolor: transparent"]REG
[/TD]
[TD="class: xl67, bgcolor: transparent"]TEL 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARY JONES
[/TD]
[TD="bgcolor: transparent"]MAZDA DEMIO
[/TD]
[TD="class: xl68, bgcolor: transparent"]MAR12V
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CORSA
[/TD]
[TD="class: xl68, bgcolor: transparent"]CK56207
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VAUXHALL ZAFIRA
[/TD]
[TD="class: xl68, bgcolor: transparent"]CN52408
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DALE WORTH
[/TD]
[TD="bgcolor: transparent"]SUZUKI SX4
[/TD]
[TD="class: xl68, bgcolor: transparent"]CN60112
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VAUXHALL ASTRA VXR
[/TD]
[TD="class: xl68, bgcolor: transparent"]BF54AJK
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MEGAN PATON
[/TD]
[TD="bgcolor: transparent"]MEGANE
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VW GOLF
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FRED
[/TD]
[TD="bgcolor: transparent"]FORD KA
[/TD]
[TD="class: xl68, bgcolor: transparent"]BD36DEF
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985682
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CORSA
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45456
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985683
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BETTY
[/TD]
[TD="bgcolor: transparent"]FORD FIESTA
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45457
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985684
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JOE
[/TD]
[TD="bgcolor: transparent"]SKODA FABIA
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45458
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985685
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JOHN
[/TD]
[TD="bgcolor: transparent"]BERLINGO
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45459
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985686
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JILL
[/TD]
[TD="bgcolor: transparent"]LEXUS RX300
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45460
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985687
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARTHA
[/TD]
[TD="bgcolor: transparent"]MISUBISHI COLT
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45461
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985688
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARY BROWN
[/TD]
[TD="bgcolor: transparent"]RENAULT LAGUNA
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45462
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985689
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FRED
[/TD]
[TD="bgcolor: transparent"]CIRROEN C4
[/TD]
[TD="class: xl68, bgcolor: transparent"]BB45463
[/TD]
[TD="class: xl69, bgcolor: transparent"]077985690








[/TD]
[/TR]
</tbody>[/TABLE]

 

[megsyp]

after search entered
[TABLE="width: 312"]
<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>[TR]
[TD="width: 90, bgcolor: transparent"]SEARCH[/TD]
[TD="class: xl68, width: 171, bgcolor: #FFC000"]ford[/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]NAME[/TD]
[TD="class: xl63, bgcolor: transparent"]VEHICLE[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]TEL 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARY JONES[/TD]
[TD="bgcolor: transparent"]MAZDA DEMIO[/TD]
[TD="class: xl66, bgcolor: transparent"]MAR12V[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CORSA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK56207[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VAUXHALL ZAFIRA[/TD]
[TD="class: xl66, bgcolor: transparent"]CN52408[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DALE WORTH[/TD]
[TD="bgcolor: transparent"]SUZUKI SX4[/TD]
[TD="class: xl66, bgcolor: transparent"]CN60112[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VAUXHALL ASTRA VXR[/TD]
[TD="class: xl66, bgcolor: transparent"]BF54AJK[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MEGAN PATON[/TD]
[TD="bgcolor: transparent"]MEGANE[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VW GOLF[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]FRED[/TD]
[TD]FORD KA[/TD]
[TD="class: xl66"]BD36DEF[/TD]
[TD="class: xl67"]077985682[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CORSA[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45456[/TD]
[TD="class: xl67, bgcolor: transparent"]077985683[/TD]
[/TR]
[TR]
[TD]BETTY[/TD]
[TD]FORD FIESTA[/TD]
[TD="class: xl66"]BB45457[/TD]
[TD="class: xl67"]077985684[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JOE[/TD]
[TD="bgcolor: transparent"]SKODA FABIA[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45458[/TD]
[TD="class: xl67, bgcolor: transparent"]077985685[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JOHN[/TD]
[TD="bgcolor: transparent"]BERLINGO[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45459[/TD]
[TD="class: xl67, bgcolor: transparent"]077985686[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JILL[/TD]
[TD="bgcolor: transparent"]LEXUS RX300[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45460[/TD]
[TD="class: xl67, bgcolor: transparent"]077985687[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARTHA[/TD]
[TD="bgcolor: transparent"]MISUBISHI COLT[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45461[/TD]
[TD="class: xl67, bgcolor: transparent"]077985688[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARY BROWN[/TD]
[TD="bgcolor: transparent"]RENAULT LAGUNA[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45462[/TD]
[TD="class: xl67, bgcolor: transparent"]077985689[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FRED[/TD]
[TD="bgcolor: transparent"]CIRROEN C4[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45463[/TD]
[TD="class: xl67, bgcolor: transparent"]077985690[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NEIL TAYLOR[/TD]
[TD="bgcolor: transparent"]SUZUKI WAGON R[/TD]
[TD="class: xl66, bgcolor: transparent"]BB45464[/TD]
[TD="class: xl67, bgcolor: transparent"]077985691[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DARREN BROWN[/TD]
[TD="bgcolor: transparent"]CITROEN PICASSO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61567[/TD]
[TD="class: xl67, bgcolor: transparent"]077985692[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CHEVROLET MATIZ[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61568[/TD]
[TD="class: xl67, bgcolor: transparent"]077985693[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEUGEOT 206[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61569[/TD]
[TD="class: xl67, bgcolor: transparent"]077985694[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JEFF[/TD]
[TD="bgcolor: transparent"]RENAULT SCENIC[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61570[/TD]
[TD="class: xl67, bgcolor: transparent"]077985695[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEUGEOT 206[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61571[/TD]
[TD="class: xl67, bgcolor: transparent"]077985696[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AUDI S LINE[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61572[/TD]
[TD="class: xl67, bgcolor: transparent"]077985697[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD FIESTA[/TD]
[TD="class: xl66"]CK61573[/TD]
[TD="class: xl67"]077985698[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SEAT[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61574[/TD]
[TD="class: xl67, bgcolor: transparent"]077985699[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DARREN GREEN[/TD]
[TD="bgcolor: transparent"]MERCEDES[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61575[/TD]
[TD="class: xl67, bgcolor: transparent"]077985700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]FIESTA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61576[/TD]
[TD="class: xl67, bgcolor: transparent"]077985701[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]KA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61577[/TD]
[TD="class: xl67, bgcolor: transparent"]077985702[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MEGANE[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61578[/TD]
[TD="class: xl67, bgcolor: transparent"]077985703[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]FIAT[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61579[/TD]
[TD="class: xl67, bgcolor: transparent"]077985704[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]KIA CARENS[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61580[/TD]
[TD="class: xl67, bgcolor: transparent"]077985705[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEUGEOT 206[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61581[/TD]
[TD="class: xl67, bgcolor: transparent"]077985706[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CITROEN XSARA PICASSO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61582[/TD]
[TD="class: xl67, bgcolor: transparent"]077985707[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD[/TD]
[TD="class: xl66"]CK61583[/TD]
[TD="class: xl67"]077985708[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MAZDA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61584[/TD]
[TD="class: xl67, bgcolor: transparent"]077985709[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD FIESTA[/TD]
[TD="class: xl66"]CK61585[/TD]
[TD="class: xl67"]077985710[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD FOCUS[/TD]
[TD="class: xl66"]CK61586[/TD]
[TD="class: xl67"]077985711[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEUGEOT 207[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61587[/TD]
[TD="class: xl67, bgcolor: transparent"]077985712[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD FIESTA[/TD]
[TD="class: xl66"]CK61588[/TD]
[TD="class: xl67"]077985713[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]KIA VENGA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61589[/TD]
[TD="class: xl67, bgcolor: transparent"]077985714[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MAZDA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61590[/TD]
[TD="class: xl67, bgcolor: transparent"]077985715[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CITROEN C2[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61591[/TD]
[TD="class: xl67, bgcolor: transparent"]077985716[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VW LUPO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61592[/TD]
[TD="class: xl67, bgcolor: transparent"]077985717[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VW LUPO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61593[/TD]
[TD="class: xl67, bgcolor: transparent"]077985718[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD ESCORT[/TD]
[TD="class: xl66"]CK61594[/TD]
[TD="class: xl67"]077985719[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MEGANE[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61595[/TD]
[TD="class: xl67, bgcolor: transparent"]077985720[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]FIESTA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61596[/TD]
[TD="class: xl67, bgcolor: transparent"]077985721[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD KA[/TD]
[TD="class: xl66"]CK61597[/TD]
[TD="class: xl67"]077985722[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VW POLO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61598[/TD]
[TD="class: xl67, bgcolor: transparent"]077985723[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FORD FIESTA[/TD]
[TD="class: xl66"]CK61599[/TD]
[TD="class: xl67"]077985724[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]CORSA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61600[/TD]
[TD="class: xl67, bgcolor: transparent"]077985725[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]INSIGNIA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61601[/TD]
[TD="class: xl67, bgcolor: transparent"]077985726[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEUGEOT 207[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61602[/TD]
[TD="class: xl67, bgcolor: transparent"]077985727[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUZUKI SWIFT[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61603[/TD]
[TD="class: xl67, bgcolor: transparent"]077985728[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]VAUXHALL ZAFIRA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61604[/TD]
[TD="class: xl67, bgcolor: transparent"]077985729[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ASTRA[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61605[/TD]
[TD="class: xl67, bgcolor: transparent"]077985730[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]RENAULT CLIO[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61606[/TD]
[TD="class: xl67, bgcolor: transparent"]077985731[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AUDI TT[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61607[/TD]
[TD="class: xl67, bgcolor: transparent"]077985732[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]HYUNDAI[/TD]
[TD="class: xl66, bgcolor: transparent"]CK61608[/TD]
[TD="class: xl67, bgcolor: transparent"]077985733[/TD]
[/TR]
</tbody>[/TABLE]

 

[megsyp]

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.

 

[Peter_SSs]

The second data set highlights the complete row where "ford" could be in any cell-

.. 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
	A	B	C	D
1	SEARCH	ford
2
3
4	NAME	VEHICLE	REG	TEL 1
5	MARY JONES	MAZDA DEMIO	MAR12V
6		CORSA	CK56207
7		VAUXHALL ZAFIRA	CN52408
8	DALE WORTH	SUZUKI SX4	CN60112
9
10		VAUXHALL ASTRA VXR	BF54AJK
11	MEGAN PATON	MEGANE
12		VW GOLF
13	FRED	FORD KA	BD36DEF	77985682
14		CORSA	BB45456	77985683
15	BETTY	FORD FIESTA	BB45457	77985684
16	JOE	SKODA FABIA	BB45458	77985685
17	JOHN	BERLINGO	BB45459	77985686
18	JILL	LEXUS RX300	BB45460	77985687
19	MARTHA	MISUBISHI COLT	BB45461	77985688
20	MARY BROWN	RENAULT LAGUNA	BB45462	77985689
21	FRED	CIRROEN C4	BB45463	77985690
22
Conditional Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	1. / Formula is =SEARCH($B$1,$B5)	Abc