Solved

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

Posted on 2016-09-21
8
54 Views
Last Modified: 2016-09-22
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
Comment
Question by:edo60
8 Comments
 
LVL 23

Assisted Solution

by:Erik Bjers
Erik Bjers earned 125 total points
Comment Utility
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 250 total points
Comment Utility
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
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 125 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
I can't embed the second image above so

Screenshot-2016-09-22-06.45.27.png
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
 

Author Closing Comment

by:edo60
Comment Utility
Oh dear experts
tsm!!!
regards
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now