find the text in the cell in column A within the text in column B in the same row

[clarksonneo]

Hi,

I have written a macro but it doesn’t work.
Could you please correct the macro?

The macro can perform this task:

I will select some cells in column A.
The macro will find the text in the cell in column A within the text in column B in the same row.

For example, if cell A1’s value is John, then the word – John in cell B1 will be bold.
For example, if cell A2’s value is May, then the word – May in cell B2 will be bold.

Please see the picture and code below.

Thanks


Sub bold()

For Each x In Selection

Set i = Application.WorksheetFunction.Find(x.Value, x.Offset(0, 1).Value)
j = Len(x.Value)

With x.Offset(0, 1).Characters(Start:=i, Length:=j).Font
.FontStyle = "bold"
End With

Next

End Sub

[Kenneth Hobs]

Option Explicit

Sub BoldParts()
Dim cell As Range, acell As Range
Dim i As Integer

For Each cell In Selection
Set acell = cell.Offset(0, 1)
i = InStr(1, acell.Value2, cell.Value2, vbBinaryCompare)
If i > 0 Then acell.Characters(i, Len(cell.Value2)).Font.FontStyle = "Bold"
Next cell
End Sub

[clarksonneo]

Option Explicit

Sub BoldParts()
Dim cell As Range, acell As Range
Dim i As Integer

For Each cell In Selection
Set acell = cell.Offset(0, 1)
i = InStr(1, acell.Value2, cell.Value2, vbBinaryCompare)
If i > 0 Then acell.Characters(i, Len(cell.Value2)).Font.FontStyle = "Bold"
Next cell
End Sub

hi

thank you, you code is perfect.

can I ask you a further question?

In you macro, you set:
acell = cell.Offset(0, 1)

In my macro, I set:
i = Application.WorksheetFunction.Find(x.Value, x.Offset(0, 1).Value)

However, my method is not successful.
Excel say "object required" for the Find function.

could you please explain any mistake I have made, and how to correct?

thanks

[GTO]

Hi there,

You were trying to use the Set Statement to Set a reference to i, which is not an Object (such as a cell or a range, worksheet, etc), but rather, a Value (a number indicating at which character the worksheet function found the start of the string being looked for).

Does that make sense?

Mark