Ask Your Question

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

asked 2016-08-28 17:32:35 +0100

JG101 gravatar image

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

example 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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-11 09:55:30.315203

1 Answer

Sort by » oldest newest most voted

answered 2016-08-28 21:19:56 +0100

mark_t gravatar image

updated 2016-08-29 01:00:10 +0100

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

edit flag offensive delete link more


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?

JG101 gravatar imageJG101 ( 2016-08-28 21:38:07 +0100 )edit

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

JG101 gravatar imageJG101 ( 2016-08-28 21:49:42 +0100 )edit

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

JG101 gravatar imageJG101 ( 2016-08-28 21:51:42 +0100 )edit

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

JG101 gravatar imageJG101 ( 2016-08-28 23:14:57 +0100 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2016-08-29 00:42:26 +0100 )edit

Question Tools

1 follower


Asked: 2016-08-28 17:32:35 +0100

Seen: 2,653 times

Last updated: Aug 29 '16