Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with some specific characters | Excel Worksheet Functions | |||
How do I use specific characters to hold value | Excel Worksheet Functions | |||
Macro that returns a specific number of characters from a text str | Excel Discussion (Misc queries) | |||
Replacing specific characters | Excel Discussion (Misc queries) | |||
Pulling out specific characters | Excel Discussion (Misc queries) |