Conditional Formating a word within a sentence - ResentClosed

Question

Greetings, 


I am trying to highlight a word in red or blue that is located with a sentence in excel. The word "Reversal" exist in several sentences within column "C" (see example below). I only need the word "Reversal" to be highlighted in each row. I don't need the entire sentence to be highlighted. Could someone please try to help?

for example the word is: Reversal 

Col. A         Col. B                          Col. C 
123           structure                       Program X - Reversal 4Q2012a 
456           structure                       Structure - Reversal 3Q2012c 
789           data                              Normalize - Reversal 4Q2012d 
1010         structure                       Cash Reclass - Reversal from 136000 
1012         source                           Commission - Program Receivable 

I appreciate any help you could provide. I'd be happy to provide any additional information if needed. Thank you very much for your time. 

Configuration: Windows XP / Internet Explorer 8.0

aquarelle · Answer

Hi, 

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 · Answer

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.

aquarelle · Answer

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 : 
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."

Conditional Formating a word within a sentence - Resent

3 replies

Subscribe To Our Newsletter!