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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Colleen - 12 Sep 2005 16:19 GMT

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

Reply to this Message

Bernie Deitrick - 12 Sep 2005 17:44 GMT

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

>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
[quoted text clipped - 6 lines]
> Thank you.
> Colleen

Reply to this Message