automatically underline certain key words in all cells in a column

[skyport]

Was hoping there is a chance this can actually be done with some sort of VBA -automatically underline certain key words in all cells in a column

 

[Peter_SSs]

@mlien

Welcome to the MrExcel board!

It isn't clear where in Sheet1 you want to look for the words and underline them. However, Rick's code is looking and underlining in column K, so I suspect that may be your problem and where you could try amending the code to suit your particular circumstances.

  Range("K:K").Font.Underline = False
  For Each Cell In Intersect(Columns("K"), ActiveSheet.UsedRange)

You would also need to ensure that Sheet1 is the active sheet when the code is run or else have the code specifically refer to that sheet.

 

[mlien]

@mlien

Welcome to the MrExcel board!

It isn't clear where in Sheet1 you want to look for the words and underline them. However, Rick's code is looking and underlining in column K, so I suspect that may be your problem and where you could try amending the code to suit your particular circumstances.

  Range("K:K").Font.Underline = False
  For Each Cell In Intersect(Columns("K"), ActiveSheet.UsedRange)

You would also need to ensure that Sheet1 is the active sheet when the code is run or else have the code specifically refer to that sheet.

Thanks for the input I will give this a try!

 

[mlien]

Thank you so much for your help Peter and Rick! I changed the column to "G" and it worked flawlessly.

For my next question, I want the macro to check for words in columns G, H and I. How do I change the code so that it checks these columns and underlines the words of my choice at the same time without having to use 3 different codes.

 

[Peter_SSs]

For my next question, I want the macro to check for words in columns G, H and I. How do I change the code so that it checks these columns and underlines the words of my choice at the same time without having to use 3 different codes.

Try this modification to those same two lines

Range("G:I").Font.Underline = False
For Each Cell In Intersect(Columns("G:I"), ActiveSheet.UsedRange)

 

[mlien]

Hi Peter,

Can you help me modify the code below to that i works? Thanks!


Sub UnderlineKeyWords()
Dim AllMatches As Object
Dim itm As Variant
Dim Cell As Range


Const myCol As String = "G" '<- Column of interest


Application.ScreenUpdating = False
Columns(myCol).Font.Underline = False
With CreateObject("VBScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "\b(" & Join(Application.Transpose(Sheets("Words").Range("A1", Sheets("Words").Cells(Rows.Count, "A").End(xlUp)).Value), "|") & ")\b"
For Each Cell In Range(myCol & 1, Range(myCol & Rows.Count).End(xlUp))
Set AllMatches = .Execute(Cell.Text)
For Each itm In AllMatches
Cell.Characters(itm.firstIndex + 1, itm.Length).Font.Underline = True
Next itm
Next Cell
End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

When posting code, please use code tags to preserve the indentation which makes the code easier to read, debug and format when pasted into our own code window. My signature block below explains how.

Can you help me modify the code below to that i works?

Try

Sub UnderlineKeyWords()
  Dim AllMatches As Object
  Dim itm As Variant
  Dim Cell As Range
  Dim lr As Long

  Const myCols As String = "G:I"
  
  Application.ScreenUpdating = False
  lr = Columns(myCols).Find(What:="*", After:=Columns(myCols).Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row
  Columns(myCols).Resize(lr).Font.Underline = False
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "\b(" & Join(Application.Transpose(Sheets("Words").Range("A1", Sheets("Words").Cells(Rows.Count, "A").End(xlUp)).Value), "|") & ")\b"
    For Each Cell In Columns(myCols).Resize(lr).Cells
        Set AllMatches = .Execute(Cell.Text)
        For Each itm In AllMatches
          Cell.Characters(itm.firstIndex + 1, itm.Length).Font.Underline = True
        Next itm
    Next Cell
  End With
  Application.ScreenUpdating = True
End Sub

 

[mlien]

Hi Peter,

I ran the macro and I get this error message "Run-time error ´91´: Object variable or with block variable not set". Any ideas?

 

[Peter_SSs]

.. I get this error message "Run-time error ´91´: Object variable or with block variable not set". Any ideas?

When you get this message, if you click 'Debug' what line of the code is highlighted?

 

[mlien]

Hi Peter this is the message i get please see the gyazo file.

https://gyazo.com/3df6b1b774b35fc865e7702b814bf126

 

[Peter_SSs]

That would seem to me to indicate that you don't have anything in columns G:I, which is where in post #53 you said your data was.