Excel: Use conditional formatting to color cell with two colors based on two variables within same cell

Question

I have an excel sheet with a row where I list people's names. Each person has a different color assigned to each name. However, sometimes I have to put two names in a single cell. I'd like the colors to remain consistent, and excel has features where colors can fade in and out on a gradient.

If Bob is blue and Alice is green, and I type 'Bob' in the cell, the cell turns blue. Great! What I want is for the cell to turn half blue half green when I type 'Bob & Alice' in the cell.

Is there a way to do this without resorting to VBA? Since it's for a work project, I don't want to rely on a coding system I don't entirely understand.

Answer 1

Without VBA this is possible using one Conditional Formatting rule for each single name and for each name combination.

Example:

Conditional formatting rules are:

Use a formula to determine which cells to format.

Formulas:

Rule 1: =AND(SEARCH("bob",$A1)>SEARCH("alice",$A1))

Rule 2: =AND(SEARCH("bob",$A1)<SEARCH("alice",$A1))

Rule 3: =SEARCH("alice",$A1)

Rule 4: =SEARCH("bob",$A1)

The order and the checked "Stop If True" is important.

To set the fill effects in Format - Fill click the button [Fill Effects] below the table of the background colors:

Using VBA this could be achieved more flexible. If you are interested in a VBA solution, then please edit your question and mention this there and put the excel-vba tag to the question.