Highlighting Multiple Cells When Using Complete or Partial Text

lonewolf4001 Posts 1 Registration date Wednesday July 19, 2017 Status Member Last seen July 19, 2017 - Jul 19, 2017 at 11:28 AM
I currently work at a distribution center, and each day we perform lane assignments for outbound products.

Attached is an image of our lane assignment spreadsheet. Below each wave the running lanes will populate when a report is copied into the Ship workload tab.

Further down is a list of employees that serve as loaders.

Currently when I place an individual name from the roster list, into the dark grey cells (white when they are running), both will populate as red with dark red text. If I use that individual again in the grey area, that name will highlight yellow with dark yellow text. I used conditional formatting to achieve this.

It only does this however, when the name is exact. If I have say ‘Andres / Guadalupe’ or ‘Brian / Epifanio’ in a cell, the roster will not highlight as if Brian is by himself, and lanes 1-40 for that particular wave will not turn yellow if one cell reads Brian, and another ‘Brian / X’.

I need this to work for each individual wave.

So, what I need is cells C56-87 to highlight if a name from those cells is represented in C12 - C51, irrespective of whether they are a perfect match or a partial match. Moreover, I need cells C12-C51 to highlight another color, if the same associate is in multiple lanes. (Again 'Brian' and 'Brian / James')

I appreciate the help. http://static.ccm2.net/ccm.net/pictures/XZx1lk6QFGHSIQulchgWgiAGTyLH8u4kMVOcu1VADpl67GV2S1guTEE9bGxWmbze-lanes.png