Conditional Formating a word within a sentence - Resent

[Closed]
Report
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
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.

3 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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."
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012

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.
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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."