Conditional Format Formula

[Mista_sav]

Hi Team, im trying to write a conditional format that is like "Cells that contain a specific word" Contain being the main thing as the cells contain multiple words.

- So my range is the C column
- I want it to highlight a color if the Cell contains say the word "Woolworths" or it might say "KFC" The cells themselves might say Woolworths NZ 123456 but i only want it to trigger off the word "Woolworths"

Can i do one formula so i dont have to process multiple Conditional Format?

Because each file is new, it will have different keywords so i will just attach it to a macro to run the conditional format to highlight the cells with the desired words

 

[DanteAmor]

Check this:

Dante Amor
	C
1
2
3
4	Woolworths NZ 123456
5
6	a
7	b
8	c
9	some KFC 888
10	e
11	f
Hoja4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH("Woolworths",C1)),ISNUMBER(SEARCH("KFC",C1)))	text	NO

 

[Mista_sav]

Check this:

Dante Amor
	C
1
2
3
4	Woolworths NZ 123456
5
6	a
7	b
8	c
9	some KFC 888
10	e
11	f
Hoja4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH("Woolworths",C1)),ISNUMBER(SEARCH("KFC",C1)))	text	NO

Thanks mate but where do i put this formula?

 

[Mista_sav]

Check this:

Dante Amor
	C
1
2
3
4	Woolworths NZ 123456
5
6	a
7	b
8	c
9	some KFC 888
10	e
11	f
Hoja4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH("Woolworths",C1)),ISNUMBER(SEARCH("KFC",C1)))	text	NO

That works awesome hahah i have some with 20 words to do hahah fun!

thanks again sir

 

[Mista_sav]

Check this:

Dante Amor
	C
1
2
3
4	Woolworths NZ 123456
5
6	a
7	b
8	c
9	some KFC 888
10	e
11	f
Hoja4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH("Woolworths",C1)),ISNUMBER(SEARCH("KFC",C1)))	text	NO

how do i add a fourth and fifth and subsequent more words? I tried 4 )))) but it didnt work on the next word?

 

[Cubist]

Rather than hardcoding it in the formula, you can list them out and reference them.

Book2
	C	D	E
1	Woolworths NZ 123456		Woolworths
2			KFC
3	a		a
4	b		b
5	c
6	some KFC 888
7	e
8	f
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH($E$1:$E$4,C1)))	text	NO

 

[Mista_sav]

Rather than hardcoding it in the formula, you can list them out and reference them.

Book2
	C	D	E
1	Woolworths NZ 123456		Woolworths
2			KFC
3	a		a
4	b		b
5	c
6	some KFC 888
7	e
8	f
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=OR(ISNUMBER(SEARCH($E$1:$E$4,C1)))	text	NO

i would but he file will differ everytime and wont have the subset

 

[Mista_sav]

or could i do it on another sheet on a cloud network?

 

[Cubist]

i would but he file will differ everytime and wont have the subset

I don't know what you mean by subset. What are you referring to?

or could i do it on another sheet on a cloud network?

You can't use other workbook reference in CF.

 

[Mista_sav]

I don't know what you mean by subset. What are you referring to?


You can't use other workbook reference in CF.

So basically each cell is a word that could appear in certain cell that we download. I want it to highlight a colour depending on the word so 9 categories i would do 9 conditional formats. Otherwise id have to do like 100 CF hahah

Income	Food/Groceries	Liabilities	Insurances	Gambling	Discretionary	Utilities	Transport	Personal Care
ACC	COUNTDOWN	GEM	A M I	Bet 365	KFC	2 Degrees	NZTA	Barber
Benefit	Dairy	Afterpay	AA	Baytree	Bakeries	Contact	Allied	Bargain Chemist
Income	Four Square	Aotea	AIA	Bet	Bakery	Council	BP	Chemist
IRD	Freshchoice	Baycorp	AMI	Casino	Big Barrel	Ecotricity	Caltex	Chemist Warehouse
Payroll	Mad Butcher	Cant Wait	Ando	Ilixium	Burger King	Electric Kiwi	Fuel	Dentist
Payslip	My Food Bag	Collection House	FMG	Nicosia	Disney Plus	Elgas	Gull	Doctor
W&I	New World	Columbus Financial	Funeral	Skycity	Henrys	Flick	NPD	Hair
Wage	PAK	Credia	IAG	Spinbet	Liquorland	Frank	Shell	Pharmacy
Wages	PAK N SAVE	Credit Card	Insurance	TAB	Lotto	Genesis	Transport	Salon
WFF	Square	Credit Corp	Lantern	New Zealand Racing	McDonalds	Globug	Uber	Unichem
Superette	Crester	Lumley		Neon	Grey Power	VTNZ
	Supermarket	DCO	Provident		Netflix	Internet	Z
	Woolworths	Debt Works	Southern Cross		Prime	Mercury	Kora
	Milk Bar	DTR	Southsure		Sky	Meridian
	Milk Run	Finance Now	Soveriegn		Spotify	Nau mai
	WWNZ Online	Fines	State		Takeaway	Nova
		Flexi	Suncorp		Uber Eats	Octopus
		Geneva	Tower		Tavern	One New Zealand
		Genoa	VERO			Powershop
		Gilrose	Credicare			Pulse
		Harmoney				Rates
		Hippo				Rockgas
Home Loan				Skinny
Instant Finance				Spark
Intercoll				Vodafone
Latitude				Orcon
Loan				Waste Management
Mastercard				Laundromat
MOJ
Moneyshop
Mortgage
MTF
Nectar
Oxford
Personal Loan
Pioneer
Platinum Finance
QCARD
SBS Money
Seed
Thorn
UDC
Wallet Wizard
ZIP
Smiths City
Swoosh
Avanti