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
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
- Nov 14, 2012 at 11:36 AM
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

aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Nov 14, 2012 at 06:11 AM
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."
0
Jthomas
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012

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.
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
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 :
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."
0