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

 

[mlien]

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.

Hi Peter! Thanks for the help from the current formula in this thread. It is working fine for me now, to another question regarding the same formula, instead of UNDERLINING the specific words, is there a way to make the words in capital letters instead of underlining them? If so, what will the new formula be like?

 

[mlien]

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.

Hi Peter,

thanks for the previous help, but lets say I would want to make the specific words in capital letters instead of underline. How would I need to modify the macro to do this?

 

[Rick Rothstein]

Hi Peter,

thanks for the previous help, but lets say I would want to make the specific words in capital letters instead of underline. How would I need to modify the macro to do this?

While it is not my code, I believe if you change this line of code...

Cell.Characters(itm.firstIndex + 1, itm.Length).Font.Underline = True

to this...

Cell.Characters(itm.firstIndex + 1, itm.Length).Text = UCase(Cell.Characters(itm.firstIndex + 1, itm.Length).Text

that it should work.

 

[mlien]

While it is not my code, I believe if you change this line of code...

Cell.Characters(itm.firstIndex + 1, itm.Length).Font.Underline = True

to this...

Cell.Characters(itm.firstIndex + 1, itm.Length).Text = UCase(Cell.Characters(itm.firstIndex + 1, itm.Length).Text

that it should work.

Thanks Rick,

gave it a try but its not working, I get error message saying Compile error: Expected: list separator or )

Any ideas?


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).Text = UCase(Cell.Characters(itm.firstIndex + 1, itm.Length) . Text
Next itm
Next Cell
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Thanks Rick,

gave it a try but its not working, I get error message saying Compile error: Expected: list separator or )

Any ideas?

It appears a closing parenthesis got omitted when I copy/pasted the revised line of code into my last message. Here is what should have been posted...

Cell.Characters(itm.firstIndex + 1, itm.Length).Text = UCase(Cell.Characters(itm.firstIndex + 1, itm.Length).Text[B][COLOR="#FF0000"])[/COLOR][/B]

 

[mlien]

Thanks for the quick answers Rick. I have a question, does the formula or each cell have a limit to words it can "run the macro" on? When my texts are too long in a cell it wont capital any words anymore.

 

[Rick Rothstein]

Thanks for the quick answers Rick. I have a question, does the formula or each cell have a limit to words it can "run the macro" on? When my texts are too long in a cell it wont capital any words anymore.

I seem to remember reading somewhere that the Characters property has a limit of 255 characters, maybe your text is longer than this?

 

[mlien]

That sounds about right.. Is there a way to get rid of this limit?

 

[mlien]

That sounds about right.. Is there a way to get rid of this limit?

I could write more than 255 characters in each cell, but the macro wont work unless its less than 255 characters in each cell.

 

[Rick Rothstein]

That sounds about right.. Is there a way to get rid of this limit?

A limit is a limit... you cannot get around it... the only thing you can do is split the text into chunks of 255 characters or less and put each chunk in a cell unto itself.