How to change partial font color in a cell which is found by another cell?

[Rajib]

I want to change the partial font color (red) found in B1 match by column A.

 

[Alaa mg]

@Peter_SSs

may you mod this line

  For Each c In Range("B1:C4")

I would to be dynamic . is there any way if the range increases without I have to change the range when increase data every time?

 

[Peter_SSs]

@Peter_SSs

may you mod this line

  For Each c In Range("B1:C4")

I would to be dynamic . is there any way if the range increases without I have to change the range when increase data every time?

Perhaps you you start your own thread rather than hijack this current one. You can put a link to this thread in your new one if you want.

 

[Peter_SSs]

Thanks muccchhh... worked perfectly.

You're welcome. Thanks for the follow-up.

 

[Siegfried Wauwermans]

I was looking for a solution to this for a while now and found out it worked perfectly. Almost! I've encountered a small thing I can't fix.
As you can see from my code, I've used this to mark some words Bold in the Targeted cell.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("O:O")) Is Nothing Then
    Dim RX As Object, M As Object
    Dim a As Variant
    Dim i As Long
    
    Target.Font.Bold = False
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
    RX.MultiLine = True
    a = Keywords.Range("A1", Keywords.Range("A" & Rows.Count).End(xlUp)).Value
    For Each c In Target
    With c
        For i = 1 To UBound(a)
          RX.Pattern = a(i, 1)
          For Each M In RX.Execute(.Value)
            .Characters(M.FirstIndex + 1, Len(M)).Font.Bold = True
          Next M
        Next i
      End With
    Next c
End If
End Sub

In my list there are some words like: "Battlecry", "Charge", "Taunt", "Legendary", ... And those work fine.
But with the options: "Spell Damage", "Spell Damage +1", "Spell Damage +2", ... and "Nature Spell Damage +3"; something is going not as wanted.
In all these instances the code can't find the full string, and skips it.
I've got no clue why only "Spell Damage" works and not the other variants.

I would appreciate any help I can get on this.

 

[Peter_SSs]

I would appreciate any help I can get on this.

Welcome to the MrExcel board!

Can you use XL2BB to give us the following so we can assess the keyword list, what is in each cell, and not have to manually type out the samples?
a) The sample data, and
b) The keywords list from column A

Is the data in a formal Excel table (ListObject)?

 

[Siegfried Wauwermans]

As requested below the sample data with XL2BB.
All data is in Excel Tables.

And thx for welcoming me

Partial bold.xlsm
	A	B	C	D
1	Info		Keyword
2	Gain 1 Mana Crystals this turn only.		Battlecry
3	Deal 1 damage.		Charge
4	Battlecry: Spend all your Mana. Gain +1/+1 for each mana spent.		Choose One
5	Choose One - Summon a Jade Golem; or Shuffle 3 copies of this card into your deck.		Deathrattle
6	Quest: Summon 5 minions with 5 or more Attack. Reward: Barnabus		Discover
7	Choose One - Deal 2 damage; or Summon two 1/1 Saplings.		Jade Golem
8	Choose One - Discover a minion; or Discover a spell.		Nature Spell Damage +3
9	Deal damage equal to your hero's Attack to a minion.		Spell Damage
10	Taunt. Choose One - +1 Attack; or +1 Health.		Spell Damage +2
11	Battlecry: Gain an empty Mana Crystal. Deathrattle: Lose a Mana Crystal.		Spell Damage +3
12	Battlecry: Destroy all damaged minions.
13	Battlecry: Draw 3 cards. Put any minions you drew directly into the battlefield.
14	Nature Spell Damage +3
15	Spell Damage +1
16
Cardlist

 

[Peter_SSs]

Thanks for the samples. Sheets/ranges etc seem to have changed though so this is a bit of a guess.

I have assumed that
- the keyword list is in a table called "Table2" in a column called "Keyword" on a worksheet called "Keywords"
- the data to be bolded or not is in a table called "Table1" in a column called "Info"

One of the issues you are facing is that the "+" symbol has a special meaning when using regular expressions so that needs to be treated in a special way.

Based on the above, try this in the sheet module that contains Table1

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("Table1[Info]"))
  If Not Changed Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
    RX.MultiLine = True
    Changed.Font.Bold = False
    a = Sheets("Keywords").Range("Table2[Keyword]").Value
    For Each c In Changed
      With c
        For i = 1 To UBound(a)
          RX.Pattern = Replace(a(i, 1), "+", "\+")
          For Each M In RX.Execute(.Value)
            .Characters(M.FirstIndex + 1, Len(M)).Font.Bold = True
          Next M
        Next i
      End With
    Next c
  End If
End Sub

 

[Siegfried Wauwermans]

I didn't know about + being special, haha
This is my first time adapting and using regular expressions. Before I found this post I had no Idea it existed.

Now it works as a charm without any problems.

@Peter_SSs Thank you very much for your help.
@Rajib Thanks for reminding me I that I still needed to solve this on my own sheets. Been working on this for a very long time.

 

[Peter_SSs]

You're welcome. Glad it is resolved. Thanks for letting us know.

One thing that I forgot to mention before. This is a check for strings, not for words.
So if you enter
I recharged the battery
you will get
I recharged the battery

or
Spell Damage +25
Spell Damage +25