Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2016-09-21
Medium Priority
85 Views
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
0
Question by:Ernesto
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 23

Assisted Solution

Erik Bjers earned 500 total points
ID: 41809985
0

LVL 53

Expert Comment

ID: 41810076
for cell F5, use conditional formatting with formula such as:
``````=VLOOKUP(\$F5,\$C\$4:\$C\$19,1,FALSE)=\$F5
``````
and then copy the conditional formatting down or set its range in the wizard dialog.
Example-list_b.xlsx
0

LVL 53

Accepted Solution

Ryan Chong earned 1000 total points
ID: 41810080
sorry, the formula was accidentally placed wrong, pls use this instead:

=VLOOKUP(\$F5,\$C\$4:\$C\$19,1,FALSE)=\$F5
Example-list_c.xlsx
0

LVL 22

Assisted Solution

Roy Cox earned 500 total points
ID: 41810088
You can use COUNTIF

Enter range to contain the formatting
Screenshot-2016-09-22-06.45.27.png
0

LVL 22

Expert Comment

ID: 41810097
I can't embed the second image above so

0

LVL 32

Expert Comment

ID: 41810101
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))
``````
And then select the whole column F and make a New Rule of conditional formatting using the formula given below....
``````=COUNTIF(List,F1)>0
``````
For more details, refer to the attached.
Example-list.xlsx
0

Author Closing Comment

ID: 41811636
Oh dear experts
tsm!!!
regards
0

LVL 22

Expert Comment

ID: 41811932
Pleased to help. You can also make the list a Table which would be Dynamic.Example-list.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesnâ€™t have any problems. We are bombarded with constant distractions, whether as an overload oâ€¦
This article describes a serious pitfall that can happen when deleting shapes using VBA.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free whenâ€¦
###### Suggested Courses
Course of the Month5 days, 2 hours left to enroll

#### 670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.