Conditional Formating a word within a sentence - Resent
Closed
Jthomas
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012
-
Nov 13, 2012 at 04:47 PM
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 - Nov 14, 2012 at 11:36 AM
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 - Nov 14, 2012 at 11:36 AM
3 responses
aquarelle
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Nov 14, 2012 at 06:11 AM
Nov 14, 2012 at 06:11 AM
Hi,
Try with this macro :
Regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
Try with this macro :
Private Sub ColC_ColorText() Dim rep As Variant Dim DerCell As Integer DerCell = Range("C65536").End(xlUp).Row rep = InputBox("Enter the text you want to search :") If Not rep = False Then Dim c As Range Dim pos As Single For Each c In Range("C2:C" & DerCell) c.Font.ColorIndex = xlColorIndexAutomatic pos = InStr(c.Text, rep) Do While pos > 0 And pos <= Len(c.Text) c.Characters(pos, Len(rep)).Font.ColorIndex = 3 pos = InStr(pos + Len(rep), c.Text, rep) Loop Next End If End Sub
Regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
Jthomas
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012
Nov 14, 2012 at 09:19 AM
Nov 14, 2012 at 09:19 AM
Aquarelle,
Thank you so much for taking the time to put the formula together for me to process the work I need to do. I truly appreciate it. However, I am not so sure how to write the entire formula into excel. I have tried to use it the way below but it wasn't working. Would you be able to please write the fomula in one line for me?
this is how I wrote it but excel would not except it. Perhaps I am not doing it correctly:
Here is my population:
Today-Johnson Reversal fromXYZ
Today-Johnson Reversal fromXYZ
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Today-Johnson Reversal fromXYZ
Today-Johnson Reversal fromXYZ
Within the column containing the word "Reversal" I woulike each I type something in this column "N" for example, in each row that contains that word for excel to color the word Reversal in Red.
And so, I wrote the formula as follows:
=(DerCell=Range(N1:N200),(rep=inputbox("Reversal)),(colorindex = Red colorindexautomatic))
I would appreciate any additional help you could provide.
Thank you very much Aquarelle.
Regards,
Jocelyne.
Thank you so much for taking the time to put the formula together for me to process the work I need to do. I truly appreciate it. However, I am not so sure how to write the entire formula into excel. I have tried to use it the way below but it wasn't working. Would you be able to please write the fomula in one line for me?
this is how I wrote it but excel would not except it. Perhaps I am not doing it correctly:
Here is my population:
Today-Johnson Reversal fromXYZ
Today-Johnson Reversal fromXYZ
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Tomorrow-Michel Reclass to ZBD
Today-Johnson Reversal fromXYZ
Today-Johnson Reversal fromXYZ
Within the column containing the word "Reversal" I woulike each I type something in this column "N" for example, in each row that contains that word for excel to color the word Reversal in Red.
And so, I wrote the formula as follows:
=(DerCell=Range(N1:N200),(rep=inputbox("Reversal)),(colorindex = Red colorindexautomatic))
I would appreciate any additional help you could provide.
Thank you very much Aquarelle.
Regards,
Jocelyne.
aquarelle
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Nov 14, 2012 at 11:36 AM
Nov 14, 2012 at 11:36 AM
Re,
It is not possible to do what you want with formula. It is necessary to use a VBA macro which is a programming language to automate any excel tasks.
To open the excel visual basic editor (where you can create a macro) use the "ALT+F11" keys.
I modified my macro to make it automatic when you type something on column N like that :
Here is a link where you can download a workbook example : http://cjoint.com/12nv/BKosuDFNe7R.htm
Download it, open it and try to type everything you want on the column N to see how it works.
Use ALT+F11, to open visual basic editor and to see the macro.
Regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
It is not possible to do what you want with formula. It is necessary to use a VBA macro which is a programming language to automate any excel tasks.
To open the excel visual basic editor (where you can create a macro) use the "ALT+F11" keys.
I modified my macro to make it automatic when you type something on column N like that :
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim DerCell As Integer Dim x As String DerCell = Range("N65536").End(xlUp).Row x = "Reversal" Set MyPlage = Range("N2:N" & DerCell) Dim c As Range Dim pos As Single For Each c In MyPlage c.Font.ColorIndex = xlColorIndexAutomatic pos = InStr(c.Text, x) Do While pos > 0 And pos <= Len(c.Text) c.Characters(pos, Len(x)).Font.ColorIndex = 3 pos = InStr(pos + Len(x), c.Text, x) Loop Next End Sub
Here is a link where you can download a workbook example : http://cjoint.com/12nv/BKosuDFNe7R.htm
Download it, open it and try to type everything you want on the column N to see how it works.
Use ALT+F11, to open visual basic editor and to see the macro.
Regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."