Highlight entire rows in used range where any cell contains searched text

C

CorrieAnn

New Member
Joined
Sep 4, 2007
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I have read no less than 50+ help articles, watched at least that many tutorials and scrubbed the posts here for any insights, but can't seem to find precisely what I am looking for.

I have a spreadsheet tracking issues/tasks (range A7:AZ1005). In cell $Q$1 the user can enter a word to search through the issues/tasks. For any issue/task containing the word (in part or whole): I would like the ENTIRE row within the used range to highlight. Meaning, not the unused columns to the right.

I have tried so many variations of conditional formats, but the best I can get is to have just the cell containing the search word to highlight. This is not very effective since there are many columns of data for each record. I need the whole line to highlight.

I am not partial to using Q1 as the input method. If we can handle this with a message box or any other approach, I am open to it.

I am using MS Excel 365. The spreadsheet is stored on SharePoint for simultaneous, multiple user input.

Thank you to anyone who can assist!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Like this?
1725944008529.png


Book1
ABCDEFGHIJKLMNOPQR
1blue
2
3
4
5
6
7yellowgreenBluepink
8greenredgreyblack
9blueredgreyblack
10
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:AZ1005Expression=NOT(ISERROR(MATCH($Q$1,$A7:$AZ7,0)))textNO
 
Upvote 0
Maybe...
Book2
ABCDEFGHIJKLQ
1King
2
3
4
5
6
7Order IDOrder DateSiteSP NameCategory KingProduct NameShip ViaQuantityOrder TotalCust SurnameGenderCompany
8103-1-N1801/10/2012On SiteLaura CallahanBeveragesLakkalikööriRoad6103.8882NioFScoutmob
9102-16-N8316/08/2012On SiteJanet LeverlingDairy ProductsMozzarella di GiovanniRoad3191.7594BaerentsMOnofre
10103-24-N3724/10/2012On SiteMargaret PeacockSeafoodInlagd SillRoad20469.9764GonzalesMSosorank
11107-15-N7715/12/2013On SiteKingGrains/CerealsSingaporean Hokkien Fried MeeSea20258.7914van BolswaerdFAilaba
12105-18-N7218/06/2013On SiteJanet LeverlingBeveragesRhönbräu KlosterbierAir15252.0027DeleonFPreisvgl
13107-22-F9022/12/2013Off SiteMichael SuyamaGrains/CerealsGnocchi di nonna AliceSeaking20768.6222RichardsFAdverts
14109-26-N7926/03/2014On SiteLaura CallahanProduceUncle Bob's Organic Dried PearsAir181018.0998EscobarFSecunia
15106-3-N9203/10/2013On SiteMargaret PeacockCondimentsVegie-spreadSea201070.4828DownsFAiting
16108-16-F3616/01/2014Off SiteRobert KingBeveragesSteeleye StoutSea6592.6632CardenasFAfterbuy
17105-13-N6813/06/2013On SiteJanet LeverlingSeafoodIkuraSea5184.1556FrancoFIngatlan
18108-19-N3919/01/2014On SiteJanet LeverlingDairy ProductsMozzarella di KINGRoad15575.9622ShepardMSelfphp
19104-21-N5321/02/2013On SiteNancy DavolioBeveragesOutback LagerSea25336.7104van KuilenburgMFunlabo
20108-6-N7506/02/2014On SiteMargaret PeacockConfectionsTeatime Chocolate BiscuitsRoad25299.1018IbarraMBuykee
21108-5-F1305/01/2014Off SiteNancy DavolioSeafoodSpegesildSea35532.8132SnuifFBabyou
22110-17-N3317/04/2014On SiteRobert KingMeat/PoultryPerth PastiesAir702452.2996MontgomeryFElfagr
23107-19-F4719/11/2013Off SiteMichael SuyamaCondimentsVegie-spreadRail9584.1702van EdeMShigoto
24106-21-F4021/08/2013Off SiteMargaret PeacockDairy ProductsGudbrandsdalsostSea20642.447ForemanMMainichi
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:AZ1005Expression=ISNUMBER(MATCH("*"&$Q$1&"*",$A7:$AZ7,0))textNO
 
Last edited:
Upvote 0
MARK858 said:
Maybe...
Book2
ABCDEFGHIJKLQ
1King
2
3
4
5
6
7Order IDOrder DateSiteSP NameCategory KingProduct NameShip ViaQuantityOrder TotalCust SurnameGenderCompany
8103-1-N1801/10/2012On SiteLaura CallahanBeveragesLakkalikööriRoad6103.8882NioFScoutmob
9102-16-N8316/08/2012On SiteJanet LeverlingDairy ProductsMozzarella di GiovanniRoad3191.7594BaerentsMOnofre
10103-24-N3724/10/2012On SiteMargaret PeacockSeafoodInlagd SillRoad20469.9764GonzalesMSosorank
11107-15-N7715/12/2013On SiteKingGrains/CerealsSingaporean Hokkien Fried MeeSea20258.7914van BolswaerdFAilaba
12105-18-N7218/06/2013On SiteJanet LeverlingBeveragesRhönbräu KlosterbierAir15252.0027DeleonFPreisvgl
13107-22-F9022/12/2013Off SiteMichael SuyamaGrains/CerealsGnocchi di nonna AliceSeaking20768.6222RichardsFAdverts
14109-26-N7926/03/2014On SiteLaura CallahanProduceUncle Bob's Organic Dried PearsAir181018.0998EscobarFSecunia
15106-3-N9203/10/2013On SiteMargaret PeacockCondimentsVegie-spreadSea201070.4828DownsFAiting
16108-16-F3616/01/2014Off SiteRobert KingBeveragesSteeleye StoutSea6592.6632CardenasFAfterbuy
17105-13-N6813/06/2013On SiteJanet LeverlingSeafoodIkuraSea5184.1556FrancoFIngatlan
18108-19-N3919/01/2014On SiteJanet LeverlingDairy ProductsMozzarella di KINGRoad15575.9622ShepardMSelfphp
19104-21-N5321/02/2013On SiteNancy DavolioBeveragesOutback LagerSea25336.7104van KuilenburgMFunlabo
20108-6-N7506/02/2014On SiteMargaret PeacockConfectionsTeatime Chocolate BiscuitsRoad25299.1018IbarraMBuykee
21108-5-F1305/01/2014Off SiteNancy DavolioSeafoodSpegesildSea35532.8132SnuifFBabyou
22110-17-N3317/04/2014On SiteRobert KingMeat/PoultryPerth PastiesAir702452.2996MontgomeryFElfagr
23107-19-F4719/11/2013Off SiteMichael SuyamaCondimentsVegie-spreadRail9584.1702van EdeMShigoto
24106-21-F4021/08/2013Off SiteMargaret PeacockDairy ProductsGudbrandsdalsostSea20642.447ForemanMMainichi
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:AZ1005Expression=ISNUMBER(MATCH("*"&$Q$1&"*",$A7:$AZ7,0))textNO
Click to expand...
This one worked with just one exception: if Q1 is left blank, the entire population highlights. I had this issue before with my previously tested solutions. How do I handle this?
 
Upvote 0
Excel Formula: 
=AND($Q$1<>"",ISNUMBER(MATCH("*"&$Q$1&"*",$A7:$AZ7,0)))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
Search Conditional formatting exact match
Replies
8
Views
213
myall_blues
myall_blues
A
  • Solved
How can I search inside a range by using the Conditional Formatting to search a value by using the cell's letter and number instead of a value?
2
Replies
10
Views
195
arthurz11
A
R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
1K
etaf
etaf
R
  • Question
VBA Code to Find then Highlight Cell that is Found, then Revert to No Fill when pressing Tab (Could be any key)
Replies
1
Views
141
Micron
M
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
388
mumps
mumps

Forum statistics

Threads
1,221,418
Messages
6,159,790
Members
451,589
Latest member
Harold14

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