VBA code for Highlight Specific Text Within A Cell Based On Other Text

[Enna]

I would like to format text from another text.
For example if column "Race" =aa then it should highlight "aa" from the "Text" column PARAAORTIC, see excel excel file.
The file is large so how can I do using VBA code? Thank you.

 

[Eric W]

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Copy the following code into the window that opens:

Sub HighlightText()
Dim c1 As Range, c2 As Range, md As Variant, i As Long, w1 As String, os As Long

    Set c1 = Range("A2")
    Set c2 = Range("B2")
    
    md = Range(c1, Cells(Rows.Count, c1.Column).End(xlUp)).Value
    
    For i = 1 To UBound(md)
        If md(i, 1) <> "" Then
            w1 = c2.Cells(i, 1).Value
            os = InStr(1, w1, md(i, 1), vbTextCompare)
            While os > 0
                c2.Cells(i, 1).Characters(Start:=os, Length:=Len(md(i, 1))).Font.Color = vbRed
                os = InStr(os + 1, w1, md(i, 1), vbTextCompare)
            Wend
        End If
    Next i
            
End Sub

On the first 2 lines (that say Set), change the cell reference to the first cell with data in it for each column. Press Alt-Q to close the VBA editor. On the sheet with the data, press Alt-F8 to open the macro selector. Select HighlightText and press Run.

Hope this helps!

 

[Enna]

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Copy the following code into the window that opens:

Sub HighlightText()
Dim c1 As Range, c2 As Range, md As Variant, i As Long, w1 As String, os As Long

    Set c1 = Range("A2")
    Set c2 = Range("B2")
   
    md = Range(c1, Cells(Rows.Count, c1.Column).End(xlUp)).Value
   
    For i = 1 To UBound(md)
        If md(i, 1) <> "" Then
            w1 = c2.Cells(i, 1).Value
            os = InStr(1, w1, md(i, 1), vbTextCompare)
            While os > 0
                c2.Cells(i, 1).Characters(Start:=os, Length:=Len(md(i, 1))).Font.Color = vbRed
                os = InStr(os + 1, w1, md(i, 1), vbTextCompare)
            Wend
        End If
    Next i
           
End Sub

On the first 2 lines (that say Set), change the cell reference to the first cell with data in it for each column. Press Alt-Q to close the VBA editor. On the sheet with the data, press Alt-F8 to open the macro selector. Select HighlightText and press Run.

Hope this helps!

Sorry, it does not do anything

 

[Eric W]

I'm not sure what to tell you. Here's the results of my test:

Are you running it while the sheet with the data in it has the focus? Are you using columns A and B?

 

[Enna]

I'm not sure what to tell you. Here's the results of my test:

View attachment 48922

Are you running it while the sheet with the data in it has the focus? Are you using columns A and B?

Sorry, I switched the columns! It works wonderfully! Than you so much!!!

 

[Eric W]

Whew! You had me worried! Glad it works for you, and thanks for letting me know! ?