Sign in with
Sign up | Sign in
Your question

How to highlight or change the format of a single word in an excel file

Last response: in Windows 8
Share
April 28, 2014 8:35:42 AM

I am having a 100 column spreadsheet with different sentences. I want to highlight the similar words or change the color of the repeating words. is there any formula or vb code which will help me to find the solution.
April 28, 2014 9:24:33 AM

From the Home tab in Excel, under Styles, click Conditional Formatting. Select New Rule, and click on Format Only Unique or Duplicate Values. From the Format All menu, select Duplicate from the drop-down menu, and click Format to select formatting.
This will allow you to format duplicate values automatically.

If you want different formatting for specific cells, click Format Only Cells that Contain, and select formatting for your choice of results.

I hope that helps.

jinujk said:
I am having a 100 column spreadsheet with different sentences. I want to highlight the similar words or change the color of the repeating words. is there any formula or vb code which will help me to find the solution.


m
0
l
April 28, 2014 11:38:04 AM

for eg: I have three cells with data : hp server, hp switch, dell server. I need to make "hp" bold or change the color since it is common. I dont want to highlight the entire cell. is it possbile?

Caldrumr said:
From the Home tab in Excel, under Styles, click Conditional Formatting. Select New Rule, and click on Format Only Unique or Duplicate Values. From the Format All menu, select Duplicate from the drop-down menu, and click Format to select formatting.
This will allow you to format duplicate values automatically.

If you want different formatting for specific cells, click Format Only Cells that Contain, and select formatting for your choice of results.

I hope that helps.

jinujk said:
I am having a 100 column spreadsheet with different sentences. I want to highlight the similar words or change the color of the repeating words. is there any formula or vb code which will help me to find the solution.




m
0
l
Related resources
a c 373 * Windows 8
April 28, 2014 11:41:52 AM

I played with this, and Conditional Formatting will only highlight the entire cell.

For a specific word within a cell, that has to be VBA code.
m
0
l
April 28, 2014 11:54:34 AM

Tnx..Do you have one.. I searched but no luck.

USAFRet said:
I played with this, and Conditional Formatting will only highlight the entire cell.

For a specific word within a cell, that has to be VBA code.


m
0
l
a c 373 * Windows 8
April 28, 2014 11:59:42 AM

jinujk said:
Tnx..Do you have one.. I searched but no luck.

USAFRet said:
I played with this, and Conditional Formatting will only highlight the entire cell.

For a specific word within a cell, that has to be VBA code.




Found this. Have not tried it, so don't know if it actually works.

http://windowssecrets.com/forums/showthread.php/146908-...

Spoiler
Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range, StrFnd As String, StrTmp As String, i As Long, j As Long, x As Long
StrFnd = InputBox("What is the string to highlight", "Highlighter")
x = Len(StrFnd)
For Each Rng In Selection
With Rng
j = UBound(Split(Rng.Value, StrFnd))
If j > 0 Then
StrTmp = ""
For i = 0 To j - 1
StrTmp = StrTmp & Split(Rng.Value, StrFnd)(i)
.Characters(Start:=Len(StrTmp) + 1, Length:=x).Font.ColorIndex = 3
StrTmp = StrTmp & StrFnd
Next
End If
End With
Next Rng
Application.ScreenUpdating = True
End Sub
m
0
l
a c 373 * Windows 8
April 28, 2014 12:01:29 PM

Is there a specific reason we want to highlight "HP" instead the whole cell containing "HP" ?

HP Server
HP Server
m
0
l
!