Can you help me? I need a VBA code which makes bold a chosen word in a cell

[makiwara]

The sheet's name is: animals

A B

1. lion The lions are beatufil animals.
2. dog Dogland is the home of the animals.

I need a VBA code which examines the words in column "A" and notes the first 2 characters of the word and then if a word in column "B" starts with that 2 characters then the code makes the whole word bold in column B.

Thank you for your time and help, it means to me a lot, if you try to help me!

Have a very nice day!

 

[Scott T]

Try

Sub hlword()
Dim lr As Long
Dim startlet As String
lr = Sheets("animals").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lr
    startlet = Left(Sheets("animals").Cells(x, 1), 2)
    p = InStr(Sheets("animals").Cells(x, 2), startlet)
    e = InStr(p + 1, Sheets("animals").Cells(x, 2), " ") - 1
    Sheets("animals").Cells(x, 2).Characters(p, e - p).Font.Bold = True

Next x
 
End Sub

 

[makiwara]

Try

Sub hlword()
Dim lr As Long
Dim startlet As String
lr = Sheets("animals").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lr
    startlet = Left(Sheets("animals").Cells(x, 1), 2)
    p = InStr(Sheets("animals").Cells(x, 2), startlet)
    e = InStr(p + 1, Sheets("animals").Cells(x, 2), " ") - 1
    Sheets("animals").Cells(x, 2).Characters(p, e - p).Font.Bold = True

Next x
 
End Sub

 

[Rick Rothstein]

The sheet's name is: animals

A B

1. lion The lions are beatufil animals.
2. dog Dogland is the home of the animals.

I need a VBA code which examines the words in column "A" and notes the first 2 characters of the word and then if a word in column "B" starts with that 2 characters then the code makes the whole word bold in column B.

This code will highlight every word, not just the first word) in Column B that starts with the first two letters of the word in Column A...

[table="width: 500"]
[tr]
	[td]Sub MakeSomeWordsBold()
  Dim R As Long, x As Long, TwoLetters As String, CellText As String
  With Sheets("animals")
    For R = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
      Do
        TwoLetters = Left(.Cells(R, "A").Value, 2)
        CellText = .Cells(R, "B").Value
        x = InStr(x + 1, CellText, TwoLetters, vbTextCompare)
        If x Then .Cells(R, "B").Characters(x, InStr(x, .Cells(R, "B").Value, " ") - x + 1).Font.Bold = True
      Loop While x
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[makiwara]

Thank you for your fast reply!! I think that just one little thing is missing.
I runned the code and the result is below. (1. row: nothing happened, 2. row: "is" bold, 3. row "t" is not bold, 4. row: perfect)

Do you know, what1s the problem? Or am I doing something wrong? Thank you for your help, I own you one

lion The lions are beatuful animals.
dog Dogland is the home of the animals.
cat The cat doesn't like horses.
duck Duccckktessssttt ???

 

[makiwara]

Thank you Rick! I launched your code too, the result is good, but in the first 2 rows now there are 2 more words bold ("are" and "is") Every other row is perfect. Thank you for your time and energy!

lion The lions are beautiful animals.
dog Dogland is the home of the animals.
cat The cat doesn't like horses.
duck Duccckktessssttt ???

This code will highlight every word, not just the first word) in Column B that starts with the first two letters of the word in Column A...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeSomeWordsBold()
  Dim R As Long, x As Long, TwoLetters As String, CellText As String
  With Sheets("animals")
    For R = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
      Do
        TwoLetters = Left(.Cells(R, "A").Value, 2)
        CellText = .Cells(R, "B").Value
        x = InStr(x + 1, CellText, TwoLetters, vbTextCompare)
        If x Then .Cells(R, "B").Characters(x, InStr(x, .Cells(R, "B").Value, " ") - x + 1).Font.Bold = True
      Loop While x
    Next
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[makiwara]

Porblem detected, it was caused by me.

Works perfect! Thank you Rick you are awsome ))

Thank you Rick! I launched your code too, the result is good, but in the first 2 rows now there are 2 more words bold ("are" and "is") Every other row is perfect. Thank you for your time and energy!

lion The lions are beautiful animals.
dog Dogland is the home of the animals.
cat The cat doesn't like horses.
duck Duccckktessssttt ???

 

[Rick Rothstein]

Thank you Rick! I launched your code too, the result is good, but in the first 2 rows now there are 2 more words bold ("are" and "is") Every other row is perfect. Thank you for your time and energy!

lion The lions are beautiful animals.
dog Dogland is the home of the animals.
cat The cat doesn't like horses.
duck Duccckktessssttt ???

That is occurring because the "space" character after the word you want bolded in Column B is not a true space (ASCII 32), rather, it is a non-breaking space (ASCII 160) which usually results from copying text off of a web page. Try this code instead (it makes all spaces real spaces)...

[table="width: 500"]
[tr]
	[td]Sub MakeSomeWordsBold()
  Dim R As Long, x As Long, TwoLetters As String, CellText As String
  With Sheets("animals")
    For R = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
      .Cells(R, "B") = Replace(.Cells(R, "B"), Chr(160), " ")
      Do
        TwoLetters = Left(.Cells(R, "A").Value, 2)
        CellText = .Cells(R, "B").Value
        x = InStr(x + 1, CellText, TwoLetters, vbTextCompare)
        If x Then .Cells(R, "B").Characters(x, InStr(x, .Cells(R, "B").Value, " ") - x + 1).Font.Bold = True
      Loop While x
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[makiwara]

Thank you unique solution! Thank you very much! )))

That is occurring because the "space" character after the word you want bolded in Column B is not a true space (ASCII 32), rather, it is a non-breaking space (ASCII 160) which usually results from copying text off of a web page. Try this code instead (it makes all spaces real spaces)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeSomeWordsBold()
  Dim R As Long, x As Long, TwoLetters As String, CellText As String
  With Sheets("animals")
    For R = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
      .Cells(R, "B") = Replace(.Cells(R, "B"), Chr(160), " ")
      Do
        TwoLetters = Left(.Cells(R, "A").Value, 2)
        CellText = .Cells(R, "B").Value
        x = InStr(x + 1, CellText, TwoLetters, vbTextCompare)
        If x Then .Cells(R, "B").Characters(x, InStr(x, .Cells(R, "B").Value, " ") - x + 1).Font.Bold = True
      Loop While x
    Next
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]