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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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
Back
Top