VBA Or Conditional Formatting - Bold a word is a string within a cell if it matches a word in another cell

mateinone

New Member
Joined
Feb 1, 2012
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi
In column D I have a list of words and in column G each cell is full of sentences
I want to bold a word within the string that matches the word in the first column, it can be VBA or some sort of conditional formatting.
I thought conditional formatting first of all but could not think of a formula to work, so now thinking about the option of doing this with vba.

The word match should be case insensitive

here is basically the output I am looking for
[D1] -> Dog [G1] -> The man bought a dog on the weekend
[D2] -> Cat [G2] -> I would prefer to have a cat to a dog
[D3] -> Rat [G3] -> The rat ate the cheese
[D4] -> Pie [G4] -> For lunch I ate an pie
[D5] -> Job [G5] -> This is the best job I have ever had
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,376
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub mateinone()
   Dim Cl As Range
   Dim x As Long
   
   For Each Cl In Range("G1", Range("G" & Rows.Count).End(xlUp))
      x = 0
      If Cl.Offset(, -3).Value <> "" Then x = InStr(1, Cl.Value, Cl.Offset(, -3).Value, vbTextCompare)
      If x > 0 Then Cl.Characters(x, Len(Cl.Offset(, -3))).Font.Bold = True
   Next Cl
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,295
Office Version
  1. 2016
Platform
  1. Windows
Notes re macro per Post #2 :
If the search string (column G) contains more than one instance of the word in column D, only the first instance will be bolded.
The macro will bold partial matches (e.g catch, dogmatic).
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,295
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub mateinone()
Dim d As Range, g As Range, L%, c%
For Each d In Range("D1:D" & Cells(Rows.Count, "D").End(3).Row)
    Set g = d(1, 4)
    L = Len(d)
    If UCase(Left(g, L)) = UCase(d) And _
        Not Mid(g, L + 1, 1) Like "[A-Za-z]" Then _
        g.Characters(1, L).Font.Bold = True
    For c = 2 To Len(g) - L + 1
        If UCase(Mid(g, c, L)) = UCase(d) Then
            If Not Mid(g, c + L, 1) Like "[A-Za-z]" And _
                Not Mid(g, c - 1, 1) Like "[A-Za-z]" Then _
                g.Characters(c, L).Font.Bold = True
        End If
    Next
Next
End Sub
 

Forum statistics

Threads
1,141,282
Messages
5,705,483
Members
421,398
Latest member
Rahat Anwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top