how to format only a specific character or number in each cell withina range of cells

[Colleen]

I am sending this to several Excel newsgroups, because I am not sure
which one if the most appropriate. I apologize in advance for the
multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How
can I format only a selected character or number within each cell in a
specific range of cells? For instance if I have a cell containing
“1234234”, how can I format it so that only the 2’s are bold or are
colored? Obviously, I have a range of cells, and I want to format all
the 2’s occurring in any of the cells as either bold or colored.
Conditional formatting does not seem to allow me to do this, and I have
been unable to find a formula that will accomplish this. Can someone help?
Thank you.
Colleen

 

[Bernie Deitrick]

Colleen,

You can't do this to numbers that are actual numbers, but if you don't mind converting your numbers
to strings, you can select the cells and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim myCell As Range
Dim myStart As Integer
For Each myCell In Selection
myCell.Value = "'" & myCell.Value
myStart = 1
While InStr(myStart, myCell.Value, "2") > 0
myStart = InStr(myStart, myCell.Value, "2")
With myCell.Characters(Start:=myStart, Length:=1).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
myStart = myStart + 1
Wend
Next myCell
End Sub