Format certain words in one Cell.

[Bigmac4]

Hi All,

Hope you can help, i have 1 cell with something like -

Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple .

What i would like to do is to automatically Bold certain words, so it would look like this

Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple

Is this possible using VBA? i have a list of 10 items that i would like to Bold in the cell but the data in the cell will change so it needs to be more automatic rather than Find/replace or select and format?

Thank you in advance for any help offered.

Office 365 Apps for enterprise

 

[Peter_SSs]

@Bigmac4
Can you please confirm that if the text is ...
Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple
... and "apple" is in the list of 10 words but "Pineapple" is not, which result below do you want?

1. Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple (search is not case-sensitive and text string is found twice)
2. Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple (search is case-sensitive and text string is found)
3. Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple (search is not case-sensitive and whole 'word' is found)
4. Oranges, Pineapples, Pears, Raspberry, Strawberry, Apple (search is case-sensitive and whole 'word' is not found)
5. Something else

I assume that there are several cells to check? Where are they?

Where is the list of words/text to search for?

 

[Bigmac4]

Hi Peter,

Lets say the 10 words to search for are in cells H15:H25 and the field to check is H5 and it would be the whole word not just part of one, there would not be an instance where one word was part of another, i just used those fruits as an example.

Option 3 above is the ideal scenario please.

Thank you

 

[Peter_SSs]

Thanks for the clarification. Try this if you really only have one cell (H5) to do this for. If it is a range of values then a slightly modified version of this would be recommended.

Sub Bold_Words()
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.Regexp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Evaluate("textjoin(""|"",1,H15:H25)") & ")\b"
  With Range("H5")
    .Font.Bold = False
    For Each M In RX.Execute(.Value)
      .Characters(M.FirstIndex + 1, Len(M)).Font.Bold = True
    Next M
  End With
End Sub

Before:

After:

 

[Bigmac4]

Perfect, works a treat, managed to write in a code to copy and paste as values into cell H5 and added a button, not a very elegant method but it is as automated as i can get which will do for now. Thank you for all of your help.

 

[Peter_SSs]

Perfect, works a treat,

You're welcome. Glad it worked for you.

managed to write in a code to copy and paste as values into cell H5

I meant to include that in the code but in the end forgot. Glad you got that done yourself though. I would have done it by just adding this line of code.

Sub Bold_Words()
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.Regexp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Evaluate("textjoin(""|"",1,H15:H25)") & ")\b"
  With Range("H5")
    .Value = .Value
    .Font.Bold = False
    For Each M In RX.Execute(.Value)
      .Characters(M.FirstIndex + 1, Len(M)).Font.Bold = True
    Next M
  End With
End Sub

 

[bebo021999]

...managed to write in a code to copy and paste as values into cell H5 ...

Converting the formula in cell H5 into a value is easy, but if the data changes, cell H5 will no longer have the formula to automatically update anymore.The best way in my opinion is to embed the formula in cell H5 directly into the code and then convert it to a value. This way, every time the code is run, H5 will automatically update the result.So, could you let me know what the formula in H5 is?