SOLVED

highlight exact match text in cells automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-2418466%22%20slang%3D%22en-US%22%3Ehighlight%20exact%20match%20text%20in%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2418466%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%26nbsp%3B%20way%20to%20highlight%20(in%20bold%20)%20the%20part%20of%20the%20text%20in%20a%20range%20of%20cells%20that%20is%20exact%20match%20with%20a%20text%20in%20a%20specific%20cell%3F%3CBR%20%2F%3E%3CBR%20%2F%3Ee.g.%20cellA1%20text%20is%20SUN%3CBR%20%2F%3Eand%20the%20function%20to%20scan%20all%20cells%20which%20will%20contain%20SUN%20within%20and%20make%20it%20bold.%3CBR%20%2F%3Ee.g.%26nbsp%3B%20CellB2%20text%3A%20i%20love%20%3CSTRONG%3ESUN%3C%2FSTRONG%3Eshine%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2418466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2418574%22%20slang%3D%22en-US%22%3ERe%3A%20highlight%20exact%20match%20text%20in%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2418574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071703%22%20target%3D%22_blank%22%3E%40Sociobright%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20with%20VBA%20programming.%20Perhaps%20someone%20could%20help%20if%20you%20consider%20such%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2418599%22%20slang%3D%22de-DE%22%3ESubject%3A%20highlight%20exact%20match%20text%20in%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2418599%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071703%22%20target%3D%22_blank%22%3E%40Sociobright%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EAs%20Mr.%20Baklan%20has%20already%20informed%20you%2C%20VBA%20would%20be%20the%20best%20solution%20for%20this.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EShould%20look%20and%20send%20later...%20if%20time%20%3A).%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EUntil%20then%2C%20here%20are%20some%20alternative%20approaches%20using%20formulas.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2419140%22%20slang%3D%22en-US%22%3EBetreff%3A%20highlight%20exact%20match%20text%20in%20cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2419140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071703%22%20target%3D%22_blank%22%3E%40Sociobright%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MakeTextBold()%0A%20%20%20%20Dim%20TextToFind%20As%20String%0A%20%20%20%20Dim%20Cell%20As%20Range%0A%20%20%20%20Dim%20CellAddress%20As%20String%0A%20%20%20%20Dim%20Pos%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20TextToFind%20%3D%20Range(%22A1%22).Value%0A%20%20%20%20With%20Cells%0A%20%20%20%20%20%20%20%20'%20Change%20MatchCase%3A%3DFalse%20to%20MatchCase%3A%3DTrue%20if%20the%20search%20should%20be%20case-sensitive%0A%20%20%20%20%20%20%20%20Set%20Cell%20%3D%20.Find(What%3A%3DTextToFind%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlPart%2C%20MatchCase%3A%3DFalse)%0A%20%20%20%20%20%20%20%20If%20Not%20Cell%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20CellAddress%20%3D%20Cell.Address%0A%20%20%20%20%20%20%20%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Pos%20%3D%20InStr(1%2C%20Cell.Value%2C%20TextToFind%2C%20vbTextCompare)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Do%20While%20Pos%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cell.Characters(Start%3A%3DPos%2C%20Length%3A%3DLen(TextToFind)).Font.Bold%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Pos%20%3D%20InStr(Pos%20%2B%201%2C%20Cell.Value%2C%20TextToFind%2C%20vbTextCompare)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Loop%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20Cell%20%3D%20.FindNext(After%3A%3DCell)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Cell%20Is%20Nothing%20Then%20Exit%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20Loop%20Until%20Cell.Address%20%3D%20CellAddress%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a  way to highlight (in bold ) the part of the text in a range of cells that is exact match with a text in a specific cell?

e.g. cellA1 text is SUN
and the function to scan all cells which will contain SUN within and make it bold.
e.g.  CellB2 text: i love SUNshine

7 Replies

@Sociobright 

That's with VBA programming. Perhaps someone could help if you consider such option.

@Sociobright 

As Mr. Baklan has already informed you, VBA would be the best solution for this.

Should look and send later...if time :).

Until then, here are some alternative approaches using formulas.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Thanks Nikolino, I really appreciate your help. If you can help with VGA that will be amazing
best response confirmed by Sociobright (New Contributor)
Solution

@Sociobright 

Here is a macro:

Sub MakeTextBold()
    Dim TextToFind As String
    Dim Cell As Range
    Dim CellAddress As String
    Dim Pos As Long
    Application.ScreenUpdating = False
    TextToFind = Range("A1").Value
    With Cells
        ' Change MatchCase:=False to MatchCase:=True if the search should be case-sensitive
        Set Cell = .Find(What:=TextToFind, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not Cell Is Nothing Then
            CellAddress = Cell.Address
            Do
                Pos = InStr(1, Cell.Value, TextToFind, vbTextCompare)
                Do While Pos
                    Cell.Characters(Start:=Pos, Length:=Len(TextToFind)).Font.Bold = True
                    Pos = InStr(Pos + 1, Cell.Value, TextToFind, vbTextCompare)
                Loop
                Set Cell = .FindNext(After:=Cell)
                If Cell Is Nothing Then Exit Do
            Loop Until Cell.Address = CellAddress
        End If
    End With
    Application.ScreenUpdating = True
End Sub

@Sociobright 

Here is another example with VBA.

Press the Text Merge button and everything should appear as you want.

 

Thank you for your understanding and patience

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

As a member of this community, I provide my help for self-help without guarantee.

Please Mark and Vote this reply if it helps, as it will be beneficial to more Community members reading here.

Thank you Hans, very appreciated! Since I am a noob and just now I started learning about macros, how do I modify this macro to have it running/scanning across all the cells within a worksheet and highlighting the duplicate text keywords?

@Sociobright 

Highlighting all duplicate words in cells? That would be a daunting task!