Conditionally-like formatting just a part of a cell

[Mac]

Hello,

assume my cells ( e.g. A1:E10) are filled with words ( i.e. one word per one
cell); I want to find specific character groups, so if I am looking for a
group 'eel' and a cells contains 'peeling', I want that "eel" part to get
formatted. How could I go about this? In the next step, I would like to
obtain the character group to search for from a specific cell, like in -
"take the contents of F1 and search for it through A1:E10, if it is found,
format that part of a cell contents". Can anyone help?

 

[OssieMac]

Hi Mac,

The following takes the contents of cell F1 and finds all occurrences of the
string in the specified range. There are 2 formatting lines of code. One
formats the found string to Bold and the other formats it to Red so you
finish up with Bold Red.


Sub FindAndFormat()

Dim rngToSearch As Range
Dim rngToFind As Range
Dim strToFind As String
Dim strFirstAddr As String
Dim intFirstChr As Integer
Dim intLenStr As Integer

strToFind = Sheets("Sheet1").Range("F1")
intLenStr = Len(strToFind)

With Sheets("Sheet1")
Set rngToSearch = .Range("A1:E10")
End With

With rngToSearch
Set rngToFind = .Find(What:="eel", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngToFind Is Nothing Then
strFirstAddr = rngToFind.Address
Do
rngToFind.Characters _
(InStr(1, rngToFind.Value, strToFind), intLenStr) _
.Font.Bold = True

rngToFind.Characters _
(InStr(1, rngToFind.Value, strToFind), intLenStr) _
.Font.Color = vbRed

Set rngToFind = .FindNext(rngToFind)

Loop While Not rngToFind Is Nothing _
And rngToFind.Address <> strFirstAddr

End If
End With


End Sub

 

[OssieMac]

My apologies Mac,

I forgot to change the hard coded "eel" in the find code to the variable
that I created. Use the following instead.

Sub FindAndFormat()

Dim rngToSearch As Range
Dim rngToFind As Range
Dim strToFind As String
Dim strFirstAddr As String
Dim intFirstChr As Integer
Dim intLenStr As Integer

strToFind = Sheets("Sheet1").Range("F1")
intLenStr = Len(strToFind)

With Sheets("Sheet1")
Set rngToSearch = .Range("A1:E10")
End With

With rngToSearch
Set rngToFind = .Find(What:=strToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngToFind Is Nothing Then
strFirstAddr = rngToFind.Address
Do
rngToFind.Characters _
(InStr(1, rngToFind.Value, strToFind), intLenStr) _
.Font.Bold = True

rngToFind.Characters _
(InStr(1, rngToFind.Value, strToFind), intLenStr) _
.Font.Color = vbRed

Set rngToFind = .FindNext(rngToFind)

Loop While Not rngToFind Is Nothing _
And rngToFind.Address <> strFirstAddr

End If
End With


End Sub

 

[Mac]

Excellent, OssieMac! That is it, thank you! One more point - what would be
the best way to unset any previous formatting before proceeding with the
next? Is there e.g. a function to do like 'take range a1:e10 and clear all
formatting'?

 

[OssieMac]

Hi Mac,

Insert the following 2 lines of code
.Font.Bold = False
.Font.ColorIndex = 0

in between these 2 lines of code

With rngToSearch

Set rngToFind = .Find(What:=strToFind, _