Solved

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

Posted on 2016-09-21
8
81 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
[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
8 Comments
 
LVL 23

Assisted Solution

by:Erik Bjers
Erik Bjers earned 125 total points
ID: 41809985
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41810076
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 52

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41810080
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 20

Assisted Solution

by:Roy Cox
Roy Cox earned 125 total points
ID: 41810088
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
 
LVL 20

Expert Comment

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

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

Expert Comment

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

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
ID: 41811636
Oh dear experts
tsm!!!
regards
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41811932
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

617 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