On Tue, 23 Feb 2010 16:43:56 +0800, "ela" wrote:


"Ron Rosenfeld" wrote in message

s = InputBox("Which letter to redden?")

If s Like "[!A-Za-z]" Then
MsgBox ("Must specify a LETTER")
Exit Sub
End If


I tried to modify your code to handle multiple color replacement but failed,
as I found variable s only appears once. Sorry for never writing macro
before, would you please kindly show one more line, say, replacing for
yellow color? I guess from the extra line I can do it for remaining (e.g.
grey, brown etc. to replace words like "boy", "girl" etc)


Hi Ela,

With macros, you learn by doing.

But you will find that you need to be VERY specific in what you want to do.

For example, in your request, you wrote you wanted to act on a *SINGLE*
character, so that is what the macro does. As a matter of fact, if you input
multiple characters at the input box, it will only use the first character. You
could change the String variable to be variable length, but in the macro it is
set to a length of "1". Of course, when you cycle through the string, you need
to change the length of the fragment you are looking at to match the length of
your input string.

You did NOT indicate what you wanted to do if the cell was processed a second
time; so I reset the colors back to some nominal value (black) each time the
macro is called. So this macro will NOT do multiple colors. But you could
easily remove the lines that "reset the color" if that is not what you want.

These are things you need to think about before coding.

If you are going to replace multiple letters or short strings with different
colors, one important consideration will be how to get that information into
the macro. I suppose you could have a series of Macros for the different
colors, and cycle through a bunch of Input boxes; but this would be rather
inefficient.

A better choice would be to set up a UserForm, where you could select letters
and/or strings; along with corresponding colors. You need to decide how you
want that to look, and work.

--ron