Partial Text Search in Conditional Formatting Formula

Chris Slater

New Member
Joined
Dec 30, 2003
Messages
19
Hi
I'm trying to work out how to apply a format to each row in my data that has a specific word, "Competition", within the text of a specific column.
As a club we run a number of competitions across the year so the word "competition" will be only part of the text. ie Competition - Trophy 1, Competition - Trophy 2 etc.
The data appears in column and row format as follows:
Column A - Description - (This is the column I will be searching in)
Column B - Date
Column C - Start Time
Column D - End Time
The Competition and it's associated Trophy will appear in the Description column and I want to highlight the row from A:D whenever the text string "Competition" appears in Column A.
The problem is that, in Conditional Formatting, the "Format only cells that contain" option does only that, namely just highlight the individual cell rather than the entire row. I also believe that Conditional Formatting doesn't support wildcards so I'm stuck what do to, I'm really hoping that one of you Geniuses/Geniei (not sure which one is correct) can help me.
Any ideas?
thanks
Chris
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You must use a formula to highlight other cells.


Try:
Dante Amor
ABCD
1DescriptionDateStart TimeEnd Time
2Competition - Trophy 105/01/202309:0110:01
3some06/01/202310:0111:01
4Competition - Trophy 207/01/202311:0112:01
5data08/01/202312:0113:01
6Competition - Trophy 309/01/202313:0114:01
7xd10/01/202314:0115:01
8Competition - Trophy 411/01/202315:0116:01
9Competition - Trophy 512/01/202316:0117:01
Hoja1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D9Expression=SEARCH("Competition",$A2)textNO
 
Upvote 0
Solution
You must use a formula to highlight other cells.


Try:
Dante Amor
ABCD
1DescriptionDateStart TimeEnd Time
2Competition - Trophy 105/01/202309:0110:01
3some06/01/202310:0111:01
4Competition - Trophy 207/01/202311:0112:01
5data08/01/202312:0113:01
6Competition - Trophy 309/01/202313:0114:01
7xd10/01/202314:0115:01
8Competition - Trophy 411/01/202315:0116:01
9Competition - Trophy 512/01/202316:0117:01
Hoja1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D9Expression=SEARCH("Competition",$A2)textNO
Hi Dante, Thank you very much for that. It has worked perfectly.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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