Changing the Color of Specific Characters using a Macro
 

Could someone please help me with this problem. I am using excel to sift
through thousands of protein and amino acid codes abbreviated by letters.
These codes are chains up to 100 characters long, so it is difficult to read
them all by hand.

what i want to do it is change the color of S everytime it is immediately
followed by a # sign. I wrote a macro that will find all of these, but i
can't figure out how to highlight this specific character. I understand how
to highlight the whole cell, the first letter, or any number of letters, but
not how to highlight the letter I actually searched.

Here is the code I made:

Sub Color_Part_of_Cell()

Dim rCell As Range

For Each rCell In Selection
If InStr(1, rCell, "S#") < 0 Then
rCell.Characters(1, 1).Font.Color = vbBlue
End If
Next rCell
End Sub

-----------------
An example of what i want it R#TSFDGDFT#SEGSHU#HS#GYUGUYG.
I would want only the S with the # after it to turn red, and I would have a
whole column of these sequences, (many are much larger)


Thank you in advance,
Chris

Changing the Color of Specific Characters using a Macro
 

Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long

For Each rCell In Selection
N = InStr(1, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 2).Font.Color = vbBlue
End If
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Could someone please help me with this problem. I am using excel to sift
through thousands of protein and amino acid codes abbreviated by letters.
These codes are chains up to 100 characters long, so it is difficult to read
them all by hand.

what i want to do it is change the color of S everytime it is immediately
followed by a # sign. I wrote a macro that will find all of these, but i
can't figure out how to highlight this specific character. I understand how
to highlight the whole cell, the first letter, or any number of letters, but
not how to highlight the letter I actually searched.

Here is the code I made:

Sub Color_Part_of_Cell()

Dim rCell As Range

For Each rCell In Selection
If InStr(1, rCell, "S#") < 0 Then
rCell.Characters(1, 1).Font.Color = vbBlue
End If
Next rCell
End Sub

-----------------
An example of what i want it R#TSFDGDFT#SEGSHU#HS#GYUGUYG.
I would want only the S with the # after it to turn red, and I would have a
whole column of these sequences, (many are much larger)


Thank you in advance,
Chris

Changing the Color of Specific Characters using a Macro
 

Thank you Jim, but what if there is more than one in the same cell. I tried
that code and it only hit the first one in each cell.

Chris

"Jim Cone" wrote:


Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long

For Each rCell In Selection
N = InStr(1, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 2).Font.Color = vbBlue
End If
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Could someone please help me with this problem. I am using excel to sift
through thousands of protein and amino acid codes abbreviated by letters.
These codes are chains up to 100 characters long, so it is difficult to read
them all by hand.

what i want to do it is change the color of S everytime it is immediately
followed by a # sign. I wrote a macro that will find all of these, but i
can't figure out how to highlight this specific character. I understand how
to highlight the whole cell, the first letter, or any number of letters, but
not how to highlight the letter I actually searched.

Here is the code I made:

Sub Color_Part_of_Cell()

Dim rCell As Range

For Each rCell In Selection
If InStr(1, rCell, "S#") < 0 Then
rCell.Characters(1, 1).Font.Color = vbBlue
End If
Next rCell
End Sub

-----------------
An example of what i want it R#TSFDGDFT#SEGSHU#HS#GYUGUYG.
I would want only the S with the # after it to turn red, and I would have a
whole column of these sequences, (many are much larger)


Thank you in advance,
Chris

Changing the Color of Specific Characters using a Macro
 

Chris,
Run another loop inside the first loop...
'--
Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long
For Each rCell In Selection
N = 1
Do
N = InStr(N, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 1).Font.Color = vbRed
N = N + 1
End If
Loop Until N = 0
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Thank you Jim, but what if there is more than one in the same cell. I tried
that code and it only hit the first one in each cell.

Chris

Changing the Color of Specific Characters using a Macro
 

Jim,

Thank you so much! I only have one more question, now that your code works
beautifully, how do i make the character both bold and change color?

Chris

"Jim Cone" wrote:

Chris,
Run another loop inside the first loop...
'--
Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long
For Each rCell In Selection
N = 1
Do
N = InStr(N, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 1).Font.Color = vbRed
N = N + 1
End If
Loop Until N = 0
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Thank you Jim, but what if there is more than one in the same cell. I tried
that code and it only hit the first one in each cell.

Chris

Changing the Color of Specific Characters using a Macro
 

Insert:

rCell.Characters(N, 1).Font.Bold = True

immediately after setting the color.
--
Gary''s Student - gsnu200781


"Frustrated IT Tech" wrote:

Jim,

Thank you so much! I only have one more question, now that your code works
beautifully, how do i make the character both bold and change color?

Chris

"Jim Cone" wrote:

Chris,
Run another loop inside the first loop...
'--
Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long
For Each rCell In Selection
N = 1
Do
N = InStr(N, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 1).Font.Color = vbRed
N = N + 1
End If
Loop Until N = 0
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Thank you Jim, but what if there is more than one in the same cell. I tried
that code and it only hit the first one in each cell.

Chris

Changing the Color of Specific Characters using a Macro
 

One more tweak to allow case insensitivity

N = InStr(N, UCase(rCell.Value), "S#")


Gord Dibben MS Excel MVP

On Sat, 26 Apr 2008 04:53:00 -0700, Frustrated IT Tech
wrote:

Jim,

Thank you so much! I only have one more question, now that your code works
beautifully, how do i make the character both bold and change color?

Chris

"Jim Cone" wrote:

Chris,
Run another loop inside the first loop...
'--
Sub Color_Part_of_Cell()
Dim rCell As Range
Dim N As Long
For Each rCell In Selection
N = 1
Do
N = InStr(N, rCell.Value, "S#")
If N 0 Then
rCell.Characters(N, 1).Font.Color = vbRed
N = N + 1
End If
Loop Until N = 0
Next rCell
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Frustrated IT Tech"
wrote in message
Thank you Jim, but what if there is more than one in the same cell. I tried
that code and it only hit the first one in each cell.

Chris