Changing the Color of Specific Characters using a Macro

[Frustrated IT Tech]

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

 

[Jim Cone]

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

 

[Frustrated IT Tech]

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]

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

 

[Frustrated IT Tech]

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

 

[Gary''s Student]

Insert:

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

immediately after setting the color.

 

[Gord Dibben]

One more tweak to allow case insensitivity

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


Gord Dibben MS Excel MVP