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... 

On Tue, 23 Feb 2010 10:01:27 +0800, "ela" <ela@yantai.org> wrote:

>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... 
>

First of all, the text was be in the cell as text, and not as a result of a
formula.

1. Select the cell.  Then in the function bar, manually select each "a"
individually, and format the color.

2.  Use a VBA Macro to do the same thing.  Here's one example:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the cells you wish to process.  

Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.


=============================
Option Explicit
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
        .Value = .Text
        .Font.ColorIndex = xlAutomatic
        .Font.TintAndShade = 0
        For i = 1 To Len(.Text)
            If Mid(.Text, i, 1) = s Then
                .Characters(i, 1).Font.Color = vbRed
            End If
        Next i
    End With
Next c
End Sub
===============================

--ron

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" <ela@yantai.org> wrote in message 
news:hlvctm$ep4$1@ijustice.itsc.cuhk.edu.hk...
> 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" <ela@yantai.org> wrote in message 
news:hlvctm$ep4$1@ijustice.itsc.cuhk.edu.hk...
> 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" <ronrosenfeld@nospam.org> 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" <lhkittle@comcast.net>
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" <ela@yantai.org> wrote in message 
>news:hlvctm$ep4$1@ijustice.itsc.cuhk.edu.hk...
>> 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" <ela@yantai.org> wrote:

>
>"Ron Rosenfeld" <ronrosenfeld@nospam.org> 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" <ronrosenfeld@nospam.org> wrote in message 
news:6tg7o5d63iuuu04p7p62op4pepdo3emjhj@4ax.com...
> 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 Tue, 23 Feb 2010 23:26:08 +0800, "ela" <ela@yantai.org> wrote:

>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

If what you are wanting to do is preserve the color through different
iterations, then look at this modification of my code for hints.

You need to not only NOT reset the color and tint, but also, note that if you
are always executing .value = .text, the replacement will have the color of the
first letter.  So that has to be done more judiciously.

=========================
Option Explicit
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
        For i = 1 To Len(.Text)
            If Mid(.Text, i, 1) = s Then
                .Characters(i, 1).Font.Color = vbRed
            End If
        Next i
    End With
Next c
End Sub
================================
--ron

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:cii8o51c8o32f3runpeoitbft1b7u980f1@4ax.com...
> If what you are wanting to do is preserve the color through different
> iterations, then look at this modification of my code for hints.
>
> You need to not only NOT reset the color and tint, but also, note that if 
> you
> are always executing .value = .text, the replacement will have the color 
> of the
> first letter.  So that has to be done more judiciously.
>
> =========================
> Option Explicit
> 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
>        For i = 1 To Len(.Text)
>            If Mid(.Text, i, 1) = s Then
>                .Characters(i, 1).Font.Color = vbRed
>            End If
>        Next i
>    End With
> Next c
> End Sub
> ================================
> --ron

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

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

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

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.8
        For i = 1 To Len(.Text)
            If Mid(.Text, i, 1) = LCase(s) Then
                .Characters(i, 1).Font.Color = vbYellow
            End If
        Next i
    End With
Next c
End Sub 

On Wed, 24 Feb 2010 09:23:58 +0800, "ela" <ela@yantai.org> 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" <ronrosenfeld@nospam.org> wrote in message 
news:ep09o59ffcobnm5vng1chv2e5j0lq681q8@4ax.com...
> On Wed, 24 Feb 2010 09:23:58 +0800, "ela" <ela@yantai.org> 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" <ela@yantai.org> 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