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

C

CorrieAnn

New Member
Joined
Sep 4, 2007
Messages
36
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
317
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
391
arthurz11
A
R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
2K
etaf
etaf
R
  • Solved
How to use conditional formatting to color the row in red based on cell value in other sheet
2
Replies
14
Views
165
etaf
etaf
R
  • Question 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
255
Micron
M

Forum statistics

Threads
1,224,739
Messages
6,180,676
Members
452,993
Latest member
FDARYABEE

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