Solved

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

Posted on 2016-09-21
8
68 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
ID: 41809985
0
 
LVL 49

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 49

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
 
LVL 17

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 17

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 28

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 17

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 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

15 Experts available now in Live!

Get 1:1 Help Now