Cell Colors

W

whispagirl

I would like to create a feature within Excel where I type in a "word" and
when I do, the cell I am will automatically color fill with the text in the
cell when I press enter.

I realize you can record a macro to do this, but I have over 100 words that
need color back filled and you can only save 52 macros. I thought of "2
digit" shortcuts but with macro help I don't find where I can save except for
the standard "1 digit" shortcut.

I thought of conditional formatting but I am unfamiliar with that step and
it appears that I would only be allowed up to 3 words with this.

Can someone explain to me how it is I could do this? It is for a
spreadsheet where the "words" are actually Company/Property names that have
specific meaning when colored that is different from the other information in
the spreadsheet.

If it is with conditional formatting, then could I create a list of words
and use the same color?

I thought of VBE, but I am unfamiliar with coding in it.

All assistance will be much appreciated.
 
P

Peo Sjoblom

You could use a lookup formula in conditional formatting,
you can put a list with words (or 3 lists since you can have 3
conditions) and then hide that list, or if the numbers of characters
hard code the list in the formula. For instance if the words are in
Z1:Z20, and the typed word is in A1 and if you want to colour these with
red fonts

Select A1, do format>conditional formatting, select formula is and use

=ISNUMBER(MATCH(A1,$Z$1:$Z20,0))

click the format button and select red fonts (or if you want to change
the back ground select pattern)



Regards,


Peo Sjoblom
 
R

RagDyeR

Peo's suggestion will do exactly what you're asking for.
However, it needs this typo correction:

=ISNUMBER(MATCH(A1,$Z$1:$Z$20,0))

*AFTER* you have Conditionally Formatted A1, copy the format down the rest
of the column, as needed ... to have each cell perform as you wish.

Click in A1,
Click on the "Format Painter" icon (yellow paintbrush) on the Toolbar,
And click and drag down to copy the format to the other cells.

Peo described *one* condition.

You can add 2 more, using the same formula, where all you must change are
the references to the 2 other ranges containing the other data that will
trigger the different formats.

Your user will need to do *nothing* to activate the formats, except type in
the data.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



One problem with your response to my initial question is that I want an
instantaneous occurrence of cell color as soon as I type one of the words in
the list. If I used conditional formatting, the way you have it worded, it
would be simpler for me to just click on the "Fill Color (automatic)" on the
toolbar. I don't want to have to use the added step. I want the cell to
color automatically.

Ok let me say it a different way. Perhaps this will assist you in further
understanding my initial question ...

I do the formatting in the Excel document. Once completed with creating the
automatic formatting, I turn the spreadsheet over to another individual who
types in the data. The individual I turn the automatically formatted
document knows nothing of how to format the Excel document to automatically
achieve the cell color nor do they wish to learn.

Now do you understand why I am requesting what I have? Perhaps now you
could give me an option to actually perform what I am wanting so it is
automatic and not just each time I need to code?
 
W

whispagirl

Hi -

Do I put this formula in "Conditional Formatting" and if so, how? I looked
to put this formula in and I am not given these options. Is this something I
need to be in VBE mode to do?

When I put the "formula" into the first cell as I would for any formula, it
gives me an error of "circular reference" and I notice it takes out the
verbage text and makes it become "0" in the cell. So obivously that is not
correct. When I set the formula in another cell with the proper sequence of
information, it says "true". But, when I type what is in the first cell, and
hit enter, nothing - no special formatting occurs.

Obviously there is some step that I am missing in your instructions. If you
fully believe your formula achieves what I want, then I ask that you put step
by step instructions here for me to follow so that I might achieve it as
well. As you have it now, you are assuming that I have worked with
conditional formatting previously and I refer you to my initial email that
clearly states I have not.
 
M

Manju

you are entering the formula in spreadsheet. you are required to put
this in the box when you select Format>conditional formatting
you would normally see condition1, cell value is. change that to
formula is and then enter the given formula in the box and select the
type of formatting you want if the condition mets in the same window.
 
R

Ragdyer

Let's say you have those 100 names.

Divide them into 3 groups ... where each group will be displayed as a
particular color when entered into Column A.
They *don't* have to be equally divided.

Say you enter 30 into X1 to X30;
Another 30 into Y1 to Y30;
And the final 40 into Z1 to Z40.

Say you're going to eventually enter these names in Column A, from A1 to
A1000.

To eliminate the necessity of copying the format to the other cells after
creation, let's select *all* the pertinent cells at the outset, and have
them formatted all at the same time.
Select A1 to A1000, with the focus of the selection in cell A1 (colored
white, where the rest of the selection is black).

While all the cells are *still* selected, click on,
<Format> <Conditional Format>
Change "Cell Value Is" to "Formula Is",
And enter this formula:

=ISNUMBER(MATCH(A1,$X$1:$X$30,0))

Then click on "Format", and choose a font color and/or pattern (fill) color
to your liking.
Click <OK>

Then click on "ADD", for your second condition.
Once again change "Cell Value Is" to "Formula Is",
And enter this formula:

=ISNUMBER(MATCH(A1,$Y$1:$Y$30,0))

Then again click on "Format", and choose a font color and/or pattern (fill)
color to your liking for this second set of names.
Click <OK>

Then click on "ADD", for your last condition.
Again change "Cell Value Is" to "Formula Is",
And enter this formula:

=ISNUMBER(MATCH(A1,$Z$1:$Z$40,0))

And repeat the actions to select the 3rd choice of formats for this last
data set.

Then <OK> <OK> to finish.

Because you used the *relative* reference A1 in all your formulas, *all* the
selected cells will acquire the format formulas.
 
Top