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 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 - 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 responses

aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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."
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.
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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."