Color and underline a single word within a cell

[giwdal]

What macro can I use to both make a specific word within a cell both red and underlined?

For example, I would like to make the word "must" wherever it is in any cell both red and underlined.

Thank you!

 

[iggydarsa]

Maybe this will give you a head start...
the following code underlines and makes the font red for the word "must" on the selected cell. So you might wanna loop or modify based on your needs.

Sub test()
    With ActiveCell.Characters(Start:=WorksheetFunction.Find("must", ActiveCell), Length:=4).Font
        .Underline = xlUnderlineStyleSingle
        .Color = -16776961
    End With
End Sub

 

[hiker95]

giwdal,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a beginning macro solution, based on iggydarsa's code, for you to consider.


The after the macro screenshot is not showing the word must correctly - please try the macro on similar data in column A, for 5 rows, A1:A5.


Sample raw data:

Excel 2007
	A
1	I would like to make the word "must" wherever it is in any cell both red and underlined.
2	make the word mustard wherever
3	make the word is not here
4	make the word "must" wherever
5	we must stop here
6
Sheet1

After the macro:

Excel 2007
	A
1	I would like to make the word "must" wherever it is in any cell both red and underlined.
2	make the word mustard wherever
3	make the word is not here
4	make the word "must" wherever
5	we must stop here
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ColorUnderlineWord()
' hiker95, 06/03/2015, ME859072
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If InStr(c, "must") Then
    With c.Characters(Start:=WorksheetFunction.Find("must", c), Length:=4).Font
      .Underline = xlUnderlineStyleSingle
      .Color = vbRed
    End With
  End If
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ColorUnderlineWord macro.

 

[giwdal]

Thanks! Much appreciated.

Excel 2007, Windows 7.

 

[hiker95]

giwdal,

Thanks! Much appreciated.

You are very welcome. Glad I could help.

Did you get a chance to test the macro?