if i have a cell as "I am a boy"

and I would like to replace all the a's in this cell with red a's

How can i achieve that? it seems Excel will replace the whole cell content
into red...

Hi Ron,

Pretty neat lil macro...!

Two questions please, I have Option Explicit active.

Dim s As String * 1

Why the... String * 1

And with this line of code

..Font.TintAndShade = 0

I have to edit out or I get an error:

Run-time 438
Object doesn't support this property or method.

VBA help says the TintAndShade value can be a -1 or 1 for light to dark.

Not a major issue in my world but just curious.

Regards,
Howard

"ela" wrote in message
...
if i have a cell as "I am a boy"

and I would like to replace all the a's in this cell with red a's

How can i achieve that? it seems Excel will replace the whole cell content
into red...

Hi Ron,

Pretty neat lil macro...!

Two questions please, I have Option Explicit active.

Dim s As String * 1

Why the... String * 1

And with this line of code

..Font.TintAndShade = 0

I have to edit out or I get an error:

Run-time 438
Object doesn't support this property or method.

VBA help says the TintAndShade value can be a -1 or 1 for light to dark.

Not a major issue in my world but just curious.

Regards,
Howard

"ela" wrote in message
...
if i have a cell as "I am a boy"

and I would like to replace all the a's in this cell with red a's

How can i achieve that? it seems Excel will replace the whole cell content
into red...

"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)

On Mon, 22 Feb 2010 20:17:43 -0800, "L. Howard Kittle"
wrote:

Hi Ron,

Pretty neat lil macro...!

Two questions please, I have Option Explicit active.

Dim s As String * 1

Why the... String * 1

And with this line of code

.Font.TintAndShade = 0

I have to edit out or I get an error:

Run-time 438
Object doesn't support this property or method.

VBA help says the TintAndShade value can be a -1 or 1 for light to dark.

Not a major issue in my world but just curious.

Regards,
Howard

"ela" wrote in message
...
if i have a cell as "I am a boy"

and I would like to replace all the a's in this cell with red a's

How can i achieve that? it seems Excel will replace the whole cell content
into red...



Thanks.

String * 1 declares a String constant with a length of one. Look up
fixed-length strings. If you try to enter a longer string, s will return just
the first letter. And the OP wrote he wanted to change "single" character.

With regard to TintAndShade, that was added in 2007, and can be a value
*BETWEEN* -1 and +1.



--ron

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

"Ron Rosenfeld" wrote in message
...
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


Hi Ron,

I comment two lines (should deal with marker background and color tuning?)
and still the codes convert all characters to green, would you please kindly
tell me which line is to reset? Sorry for asking for your help but I really
only need this for once. ==Ela

Option Explicit
Sub RedLetter()
Dim a As String * 1
Dim t As String * 1
Dim c As String * 1
Dim g As String * 1
Dim ch As Range
Dim i As Long

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

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

For Each ch In Selection
With ch
.Value = .Text
' .Font.ColorIndex = xlAutomatic
' .Font.TintAndShade = 0
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) = a Then
.Characters(i, 1).Font.Color = vbRed
End If
Next i
End With
Next ch

t = InputBox("Which letter to green?")

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

For Each ch In Selection
With ch
.Value = .Text
' .Font.ColorIndex = xlAutomatic
' .Font.TintAndShade = 0
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) = t Then
.Characters(i, 1).Font.Color = vbGreen
End If
Next i
End With
Next ch
End Sub

On Wed, 24 Feb 2010 09:23:58 +0800, "ela" wrote:

I repeat the Sub several times to test, but the results were unexpected.
First, I use LCase so to make the search case-insensitive but failed. No
matter I input an upper "K" or the lower one "k", it only replaces the lower
"k" for me. Second, I found the yellow color too bright to visualize and so
re-used .Font.TintAndShade (I also tried negative values). Again, the color
was still very bright. I'm using Excel2007 and so expect the color range
should be very wide...


Sub RedLetter()
Dim s As String * 1
Dim c As Range
Dim i As Long

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

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

For Each c In Selection
With c
If .HasFormula Then .Value = .Text
.Font.TintAndShade = 0.5
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) = LCase(s) Then
.Characters(i, 1).Font.Color = vbRed
End If
Next i
End With
Next c
End Sub

If you want your comparison to be case insensitive, you have to either

ensure both sides of your comparison are the same case

If Lcase(Mid(.Text, i, 1)) = LCase(s) Then

OR you can set Option Compare Text at the beginning of your macro.

So far as how the color appears, you'll have to experiment. Or you can try
varieties of the colorindex property.
--ron

"Ron Rosenfeld" wrote in message
...
On Wed, 24 Feb 2010 09:23:58 +0800, "ela" wrote:

I repeat the Sub several times to test, but the results were unexpected.
First, I use LCase so to make the search case-insensitive but failed. No
matter I input an upper "K" or the lower one "k", it only replaces the
lower
"k" for me. Second, I found the yellow color too bright to visualize and
so
re-used .Font.TintAndShade (I also tried negative values). Again, the
color
was still very bright. I'm using Excel2007 and so expect the color range
should be very wide...


Sub RedLetter()
Dim s As String * 1
Dim c As Range
Dim i As Long

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

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

For Each c In Selection
With c
If .HasFormula Then .Value = .Text
.Font.TintAndShade = 0.5
For i = 1 To Len(.Text)
If Mid(.Text, i, 1) = LCase(s) Then
.Characters(i, 1).Font.Color = vbRed
End If
Next i
End With
Next c
End Sub

If you want your comparison to be case insensitive, you have to either

ensure both sides of your comparison are the same case

If Lcase(Mid(.Text, i, 1)) = LCase(s) Then

OR you can set Option Compare Text at the beginning of your macro.

So far as how the color appears, you'll have to experiment. Or you can
try
varieties of the colorindex property.
--ron


Thanks a lot!! although the macro looks clumsy but it works well now!!!
Thanks again, Ron~~~

--Ela

On Wed, 24 Feb 2010 10:15:28 +0800, "ela" wrote:

Thanks a lot!! although the macro looks clumsy but it works well now!!!
Thanks again, Ron~~~

--Ela

Glad to help. Thanks for the feedback.

And when you get a chance, take a look at user forms to make your parameter
input a bit easier.
--ron