Color format Cell by duplicate character / name / value in each column / cell

How would I be able to color code each column that represent a certain name or value

column A: mike Column B: John Column C: Dan Column D: mike Column E: Dan Column E: John

I would like all cells with the name " mike " color code RED

is there a way to do this with conditional formatting.

is there a different way?

thank you in advance for any help provided.

Easiest way is conditional formatting.

Either icon on toolbar or Format, Conditional Formatting, Condition…

“Cell Value is”, “Equal to” and enter your name in a string, “mike”

Select new style and set up the format you want.

Set the range of cells you want to apply the condition to.

Edit in answer to comment.
A very messy alternate to conditional formatting is to use the STYLE function with a lookup table to find the style for each name.


However you need to then always copy the formula when you add the name to a different cell.

I think it might be better to use multiple conditional formats, unless you create a macro that runs on any cell change and uses a lookup table to select the format.

Edit to add sample Spreadsheet document with macro on event for cell content changed.
Untitled 15.ods

wow Thank you Mark, did not realize i could use Letters as values.

Is there a way to copy an entire conditional format so I do not have to re-enter every time.
I have 32 conditions I would like to create under one conditional format: is this possible?

Mark, I tried this. Why does Conditional Format turn every thing lower case. My Names all start with Capital first letter of each name.

How can this be fixed where conditional format recognizes a direct copy / paste

Mark, I tried changing my name to lower case for some reason its not working

Mark, thank you for the suggestions.

I was thinking of using an INDEX , MATCH function to show cell color: but then probably not going to work.

I created a list of name’s and in the next column color coded to represent each name, is there a way to have the cell color code when the name matches corresponding to the color code

Be aware that use STYLE function in conditional format makes it very slow, and it will not be solved. UI: Use of STYLE() in conditional formatting results in continuous CPU usage