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
ErnestoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ryan ChongConnect With a Mentor Commented:
sorry, the formula was accidentally placed wrong, pls use this instead:

=VLOOKUP($F5,$C$4:$C$19,1,FALSE)=$F5
SnapShot.pngExample-list_c.xlsx
0
 
Ryan ChongCommented:
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.
SnapShot.pngSnapShot1.pngExample-list_b.xlsx
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
You can use COUNTIF

Screenshot-2016-09-22-06.39.10.png
Enter range to contain the formatting
Screenshot-2016-09-22-06.45.27.png
0
 
Roy CoxGroup Finance ManagerCommented:
I can't embed the second image above so

Screenshot-2016-09-22-06.45.27.png
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
 
ErnestoAuthor Commented:
Oh dear experts
tsm!!!
regards
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help. You can also make the list a Table which would be Dynamic.Screenshot-2016-09-23-06.01.43.pngExample-list.xlsx
0
All Courses

From novice to tech pro — start learning today.