Custom format text

Good morning all,
I have in column E, from cell E1 to E300, text information all over black
color.
Each cell has about 50 characters.
I want to search all these cells for the string �ZZZ� and if it find the
string then change ZZZ color from black to red. Only ZZZ needs to be change
to red and not the whole text in the cell.

Thanks

Coco

Hi,
The following code changes the color of the 1st instance of the
search-string in each cell of the current selection.
'----------------------------------------------------------
Sub test()
Dim rg As Range, cell As Range
Dim txt As String, searchFor As String
Dim pos As Long, lenSearch As Long

'-------- CHANGE HERE ---------------------
Set rg = Selection
searchFor = "zzz"
'-----------------------------------------------

lenSearch = Len(searchFor)
For Each cell In rg.Cells
If Not rg.HasFormula Then 'If not formula
txt = cell.Text
pos = InStr(txt, searchFor)
If pos > 0 Then 'Set Color
cell.Characters(pos, lenSearch).Font.Color = vbRed
End If
End If
Next

End Sub
'-----------------------------------------------------------
To modify the color for all instances within a cell, modify the code within
the:
If Not rg.HasFormula Then
to loop within the string.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"coco" wrote:

> Good morning all,
> I have in column E, from cell E1 to E300, text information all over black
> color.
> Each cell has about 50 characters.
> I want to search all these cells for the string “ZZZ” and if it find the
> string then change ZZZ color from black to red. Only ZZZ needs to be change
> to red and not the whole text in the cell.
>
> Thanks
>
> Coco
>

This was fast.
perfect!!!, it works fine
Thanks you Sébastien

Coco

"sebastienm" wrote:

> Hi,
> The following code changes the color of the 1st instance of the
> search-string in each cell of the current selection.
> '----------------------------------------------------------
> Sub test()
> Dim rg As Range, cell As Range
> Dim txt As String, searchFor As String
> Dim pos As Long, lenSearch As Long
>
> '-------- CHANGE HERE ---------------------
> Set rg = Selection
> searchFor = "zzz"
> '-----------------------------------------------
>
> lenSearch = Len(searchFor)
> For Each cell In rg.Cells
> If Not rg.HasFormula Then 'If not formula
> txt = cell.Text
> pos = InStr(txt, searchFor)
> If pos > 0 Then 'Set Color
> cell.Characters(pos, lenSearch).Font.Color = vbRed
> End If
> End If
> Next
>
> End Sub
> '-----------------------------------------------------------
> To modify the color for all instances within a cell, modify the code within
> the:
> If Not rg.HasFormula Then
> to loop within the string.
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "coco" wrote:
>
> > Good morning all,
> > I have in column E, from cell E1 to E300, text information all over black
> > color.
> > Each cell has about 50 characters.
> > I want to search all these cells for the string “ZZZ” and if it find the
> > string then change ZZZ color from black to red. Only ZZZ needs to be change
> > to red and not the whole text in the cell.
> >
> > Thanks
> >
> > Coco
> >