Report

Highlight cells with Text that match data validation menu [Solved]

Ask a question Gamul1 2Posts Thursday September 22, 2016Registration date September 22, 2016 Last seen - Last answered on Sep 22, 2016 10:04AM
I see several similar questions on the forum but not quite the same and the answers don't seem to extend to my situation.

I have an Excel sheet that schedules people for weekend support. I am also using a data validation cell/menu where I can select the engineer from the drop down menu. What I want is when I select that name from the validation cell, I want any of the cells in the column to highlight if that person is in that list.

So - I have Column B with a list of names that may look like this:
Fred
George
Harry
George
Ron
Harry
Remus
George

In Cell C3 I have the data validation menu with the selections of: Fred; George; Harry; Remus; Ron

When I select George from the menu I want the cells in B to highlight if they have George in them.

I tried the following example I have seen in multiple places:
=ISNUMBER(SEARCH(C3,B2))

I tried that in a cell formula and I also tried setting it up in Conditional Formatting. I'm just not getting the flow right. I've tried adjusting values around but the best I came up with was cell C3 highlighting.

I don't want to hard code "George" into the formula because that name will change. In reality the list is longer, and this sheet is intended for all the team so they can find their name in the schedule quickly.

I hope I've explained myself well enough. If not, please let me know.
See more 
Helpful
+0
moins plus
Never mind. I figured it out.

I created a conditional rule for "Format only cells that contain"

Format only cells with: "Specific Text; Containing; =$C$3 (note, my full data sheet actually has the cell in I3 so that's what I used, I just kept C3 as that was my example)

In the Applies to I set it to : = $E (in Reality, my data set was $D:$F)

This now works as expected.
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!