Use conditional formating to find one word in a column.

B

Betty

I am trying to use conditional formating to find one word in a column of
cells and highlight just the one word regardless of how many words in that
cell. Has anyone come across this.
 
A

Allllen

You can't highlight just that one word with conditional formatting.
Conditional formatting is applied to the whole cell.

You can highlight all cells with the word allllen in them with something
like this in the conditional formatting for cell A1:
Formula is =NOT(ISERROR(FIND("allllen",A1)))

then copy, edit > pastespecial > formats to other cells you want to
highlight.

You could conceivably highlight just that one word with some *very* advanced
programming but that is way beyond me.
 
B

Bernie Deitrick

Betty,

CF won't work - you need to use VBA: see the macro below.

HTH,
Bernie
MS Excel MVP

Sub RedBold()
Dim c As Range
Dim d As Range
Dim mycell As Range
Dim myStart As Integer
Dim mylen As Integer
Dim myFindString As String
Dim firstAddress As String

myFindString = InputBox("Enter the word(s) to make Red & Bold", , "Betty")

'find the instances
With Application.InputBox("Select the Range", Type:=8)

Set c = .Find(myFindString, LookIn:=xlValues, LookAt:=xlPart)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

'Change to Red and Bold
For Each mycell In d
myStart = InStr(1, mycell.Value, myFindString)
mylen = Len(myFindString)
With mycell.Characters(Start:=myStart, Length:=mylen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
Next mycell

End Sub
 
Top