asked on

Colored cell if an input text is contained in a given list

Hi dear experts, I need to colored cell in blue when user input contain a given list

i attach an example of what i need the rule do,
regards you experts

edo
Example-list.xlsx

SOLUTION

Member_2_2473503

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Ryan Chong

for cell F5, use conditional formatting with formula such as:

=VLOOKUP($F5,$C$4:$C$19,1,FALSE)=$F5

Open in new window

and then copy the conditional formatting down or set its range in the wizard dialog.

Example-list_b.xlsx

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Roy Cox

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Roy Cox

I can't embed the second image above so

Subodh Tiwari (Neeraj)

The best way is to create a dynamic named range which will contain your list using the formula given below....
List:

=OFFSET(Hoja1!$C$4,,,COUNTA(Hoja1!$C$4:$C$100))

Open in new window

And then select the whole column F and make a New Rule of conditional formatting using the formula given below....

=COUNTIF(List,F1)>0

Open in new window

For more details, refer to the attached.
Example-list.xlsx

Ernesto

ASKER

Oh dear experts
tsm!!!
regards

Roy Cox

Pleased to help. You can also make the list a Table which would be Dynamic.

Example-list.xlsx