Highlight cells with Text that match data validation menu

Solved/Closed
Gamul1 Posts 2 Registration date Thursday September 22, 2016 Status Member Last seen September 22, 2016 - Sep 22, 2016 at 09:50 AM
Gamul1 Posts 2 Registration date Thursday September 22, 2016 Status Member Last seen September 22, 2016 - Sep 22, 2016 at 10:04 AM
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.

1 response

Gamul1 Posts 2 Registration date Thursday September 22, 2016 Status Member Last seen September 22, 2016
Sep 22, 2016 at 10:04 AM
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.
0