I have 3 letter codes that I would like to color code so that whenever I type those 3 letter codes anywhere in the spread sheet the cell changes to the color assigned to that specific code. How do I set this up?
I have 3 letter codes that I would like to color code so that whenever I type those 3 letter codes anywhere in the spread sheet the cell changes to the color assigned to that specific code. How do I set this up?
One way via conditional formatting
Assume the 3 letter codes are: ABC, DEF, GHI
Press CTRL+A (Selects the entire sheet)
Click Format > Conditional Formatting
and set for Conditions 1 to 3, the "Formula Is:"
=ISNUMBER(FIND("ABC",A1))
=ISNUMBER(FIND("DEF",A1))
=ISNUMBER(FIND("GHI",A1))
Format to taste the fill color for each of the 3 conditions, OK out
Note: Replace FIND with SEARCH in the cond format formulas above
if case sensitivity for the 3 letter codes is not important
(FIND is case sensitive)
Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jdiedrick" <[email protected]> wrote
in message news:[email protected]...
>
> I have 3 letter codes that I would like to color code so that whenever I
> type those 3 letter codes anywhere in the spread sheet the cell changes
> to the color assigned to that specific code. How do I set this up?
>
>
> --
> jdiedrick
> ------------------------------------------------------------------------
> jdiedrick's Profile:
http://www.excelforum.com/member.php...o&userid=27125
> View this thread: http://www.excelforum.com/showthread...hreadid=466609
>
I would expect if it take three letters do describe the color that
a choice of more than 3 colors (and 1 default) is wanted.
The normal solution is to use the change event for changes
that are typed in constants (not formulas). see
http://www.mvps.org/dmcritchie/excel/event.htm#case
For those that would rather use an addin that extends the limit
of 3 Conditional Formatting conditions per cell you take a look
at CFPlus - Extended Conditional Formatter, Bob Phillips
http://www.xldynamic.com/source/xld.....Download.html
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Max" <[email protected]> wrote in message news:%[email protected]...
> One way via conditional formatting
>
> Assume the 3 letter codes are: ABC, DEF, GHI
>
> Press CTRL+A (Selects the entire sheet)
>
> Click Format > Conditional Formatting
> and set for Conditions 1 to 3, the "Formula Is:"
> =ISNUMBER(FIND("ABC",A1))
> =ISNUMBER(FIND("DEF",A1))
> =ISNUMBER(FIND("GHI",A1))
> Format to taste the fill color for each of the 3 conditions, OK out
>
> Note: Replace FIND with SEARCH in the cond format formulas above
> if case sensitivity for the 3 letter codes is not important
> (FIND is case sensitive)
>
> Adapt to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "jdiedrick" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I have 3 letter codes that I would like to color code so that whenever I
> > type those 3 letter codes anywhere in the spread sheet the cell changes
> > to the color assigned to that specific code. How do I set this up?
> >
> >
> > --
> > jdiedrick
> > ------------------------------------------------------------------------
> > jdiedrick's Profile:
> http://www.excelforum.com/member.php...o&userid=27125
> > View this thread: http://www.excelforum.com/showthread...hreadid=466609
> >
>
>
"David McRitchie" wrote:
> I would expect if it take three letters do describe the color that
> a choice of more than 3 colors (and 1 default) is wanted ..
Good additions, thanks. Guess I was so mesmerised by the magical number 3
in the OP's "3 letter codes" that I took it to mean there were only 3 codes
involved (besides each code having 3 letters, that is) <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks