replace a single character WITH FORMAT in a cell

  • Thread starter Thread starter ela
  • Start date Start date
E

ela

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...
 
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...
Click to expand...

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
 
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
 
Ron Rosenfeld said:
s = InputBox("Which letter to redden?")

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

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 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
Click to expand...

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
 
Ron Rosenfeld said:
s = InputBox("Which letter to redden?")

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

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)
Click to expand...

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 said:
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
Click to expand...

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
 
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
Click to expand...

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 said:
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
Click to expand...

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
 
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
Click to expand...

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 said:
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
Click to expand...

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
Click to expand...

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

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

--Ela
Click to expand...

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
 
Back
Top