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

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

mark_t

1035 ●6 ●11 ●17

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

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.

="Group"&T(STYLE(VLOOKUP(CURRENT(),$Sheet3.$A$2:$B$3,2,0)))

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

Comments

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 ( 2016-08-28 21:38:07 +0200 )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 ( 2016-08-28 21:49:42 +0200 )edit

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

JG101 ( 2016-08-28 21:51:42 +0200 )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 ( 2016-08-28 23:14:57 +0200 )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 ( 2016-08-29 00:42:26 +0200 )edit

add a comment

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

1 Answer

Comments

Question Tools

Stats

Related questions

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

1 Answer

Comments

Question Tools

Stats

Related questions

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