Solved

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

Posted on 2016-09-21
8
78 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 19

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 19

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 19

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
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!
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

734 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