Highlight a list of word/sentence in entire workbook

[saeid025]

Hi awesome people
I have a list of important words/sentences; I want to highlight any text that is in that sheet in any of my worksheet
so lets say I have ["word", "long word"], in the following table

This is a word and this is a long word

This WorD isn't case sensitive

you can see words are highlighted.
What is the best way to do this in? I would prefer a way without macros...
Thanks!

 

[Peter_SSs]

I would prefer a way without macros...

That would not be possible so is a macro solution acceptable?

I want to highlight any text that is in that sheet in any of my worksheet

You use the term text rather than word so ..

Given your ["word", "long word"] idea above, if the value being checked was
"The soldier had two swords"

Would the text appear as it is above or as
"The soldier had two swords

 

[bebo021999]

Highlight sub-string within a master string, I think only VBA can do.

 

[saeid025]

That would not be possible so is a macro solution acceptable?


You use the term text rather than word so ..

Given your ["word", "long word"] idea above, if the value being checked was
"The soldier had two swords"

Would the text appear as it is above or as
"The soldier had two swords

I mean if its the only way I guess I'll do it...
Is it possible to read the list of words from a hidden worksheet? so if we want to change the words we can just update that hidden worksheet?
and also about the later question, yeah I want the words to be highlighted no matter where they are, so yeah swords is valid as well

 

[Peter_SSs]

I mean if its the only way I guess I'll do it...

OK, test the macro below with a copy of your workbook

Is it possible to read the list of words from a hidden worksheet?

Yes. I have used a sheet called 'Hidden' below. For this to work its best the list of words/phrases should be sorted from longest to shortest. The code below does the sorting so you can just add extra texts at the bottom of the table in the first column. Provided the table is set up correctly the length formula will automatically be inserted and my code will sort the table when it is run.

So, the Hidden sheet should be set up like this and made into a formal Excel table (post back if you need help with how to do that)

saeid025.xlsm
	A	B
1	Look For	Length
2	long word	9
3	word	4
4	is	2
5
6
Hidden

Cell Formulas
Range	Formula
B2:B4	B2	=LEN(A2)

This is the macro (again ask if you need help to implement)

Sub Highlight_Strings()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Sheets("Hidden").ListObjects(1).DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlDescending, Header:=xlNo
    RX.Pattern = Join(Application.Transpose(.Columns(1)), "|")
  End With
  With Sheets("Sheet1")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        For Each M In RX.Execute(a(i, 1))
          With .Cells(i).Characters(Start:=M.firstindex + 1, Length:=M.Length).Font
            .Color = RGB(255, 155, 0)
            .Underline = xlUnderlineStyleSingle
          End With
        Next M
      Next i
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Here is my test sheet before the code is used

saeid025.xlsm
	A
1	Data
2	This is a word and this is a long word
3	This WorD isn't case sensitive
4	No special strings here
5	The soldier had two swords
6
Sheet1

.. and after the code has been run.

 

[saeid025]

OK, test the macro below with a copy of your workbook



Yes. I have used a sheet called 'Hidden' below. For this to work its best the list of words/phrases should be sorted from longest to shortest. The code below does the sorting so you can just add extra texts at the bottom of the table in the first column. Provided the table is set up correctly the length formula will automatically be inserted and my code will sort the table when it is run.

So, the Hidden sheet should be set up like this and made into a formal Excel table (post back if you need help with how to do that)

saeid025.xlsm
	A	B
1	Look For	Length
2	long word	9
3	word	4
4	is	2
5
6
Hidden

Cell Formulas
Range	Formula
B2:B4	B2	=LEN(A2)

This is the macro (again ask if you need help to implement)

Sub Highlight_Strings()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Sheets("Hidden").ListObjects(1).DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlDescending, Header:=xlNo
    RX.Pattern = Join(Application.Transpose(.Columns(1)), "|")
  End With
  With Sheets("Sheet1")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        For Each M In RX.Execute(a(i, 1))
          With .Cells(i).Characters(Start:=M.firstindex + 1, Length:=M.Length).Font
            .Color = RGB(255, 155, 0)
            .Underline = xlUnderlineStyleSingle
          End With
        Next M
      Next i
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Here is my test sheet before the code is used

saeid025.xlsm
	A
1	Data
2	This is a word and this is a long word
3	This WorD isn't case sensitive
4	No special strings here
5	The soldier had two swords
6
Sheet1

.. and after the code has been run.

View attachment 118886

thank you very much for putting the time
a few more questions
1. is it possible to modify the macro so it only check the texts in one or more column? so in your example words will be highlighted in all cells in A and B, but not the rest?
2. can we apply this macro workbook wide? so it'll work on all worksheets?

 

[Peter_SSs]

Sorry I forgot about it being everywhere. The best approach may depend on how many sheets, how much data is on each sheet etc but give this a try instead.

Sub Highlight_Strings_v2()
  Dim RX As Object, M As Object
  Dim ws As Worksheet
  Dim a As Variant
  Dim i As Long, j As Long, uba As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Sheets("Hidden").ListObjects(1).DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlDescending, Header:=xlNo
    RX.Pattern = Join(Application.Transpose(.Columns(1)), "|")
  End With
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Hidden" Then
      With ws.UsedRange
        a = .Value
        uba = UBound(a)
        For j = 1 To UBound(a, 2)
          For i = 1 To uba
            For Each M In RX.Execute(a(i, j))
              With .Cells(i, j).Characters(Start:=M.firstindex + 1, Length:=M.Length).Font
                .Color = RGB(255, 155, 0)
                .Underline = xlUnderlineStyleSingle
              End With
            Next M
          Next i
        Next j
      End With
    End If
  Next ws
  Application.ScreenUpdating = True
End Sub

 

[saeid025]

Sorry I forgot about it being everywhere. The best approach may depend on how many sheets, how much data is on each sheet etc but give this a try instead.

Sub Highlight_Strings_v2()
  Dim RX As Object, M As Object
  Dim ws As Worksheet
  Dim a As Variant
  Dim i As Long, j As Long, uba As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Sheets("Hidden").ListObjects(1).DataBodyRange
    .Sort Key1:=.Columns(2), Order1:=xlDescending, Header:=xlNo
    RX.Pattern = Join(Application.Transpose(.Columns(1)), "|")
  End With
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Hidden" Then
      With ws.UsedRange
        a = .Value
        uba = UBound(a)
        For j = 1 To UBound(a, 2)
          For i = 1 To uba
            For Each M In RX.Execute(a(i, j))
              With .Cells(i, j).Characters(Start:=M.firstindex + 1, Length:=M.Length).Font
                .Color = RGB(255, 155, 0)
                .Underline = xlUnderlineStyleSingle
              End With
            Next M
          Next i
        Next j
      End With
    End If
  Next ws
  Application.ScreenUpdating = True
End Sub

thank you for fast update
could having a lot of different sheets that each have a good amount of data impact performance and make the excel unusable?
and also as I said is there a way to limit the range to just a few column?

 

[Peter_SSs]

could having a lot of different sheets that each have a good amount of data impact performance and make the excel unusable?

It could. Did that happen to you?

is there a way to limit the range to just a few column?

Yes. What columns on what sheets?

 

[saeid025]

It could. Did that happen to you?

not yet, I haven't tested it on large excels yet, but in my tests its ok right now.

Yes. What columns on what sheets?

In all sheets, the tricky part is I want it parse different column based on sheet name.
for example sheets that have their name started with "Key" should have the B column of them highlighted and the rest A column...
another thing that I think worth mentioning is I have a lot of sheets in my excel, but the thing with them is most of them are hidden by default, so maybe we could just highlight the current open and visible sheet words?