HI.

I need to know how to set a conditional format that would font all
numbers black but any letters gray within a cell.

Thanx.

Conditional formatting won't do this kind of character by character formatting.
(You did mean that there was a combination of numbers and letters in the same
cell, right?)



"J.W. Aldridge" wrote:
>
> HI.
>
> I need to know how to set a conditional format that would font all
> numbers black but any letters gray within a cell.
>
> Thanx.

--

Dave Peterson

Not with CF, but VBA will do it

Public Sub FormatCellLetters()
Dim sChar As String
Dim i As Long
With ActiveCell
For i = 1 To Len(.Value)
If Not IsNumeric(Mid$(.Value, i, 1)) Then
.Characters(i, 1).Font.ColorIndex = 15
End If
Next i
End With
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> HI.
>
> I need to know how to set a conditional format that would font all
> numbers black but any letters gray within a cell.
>
> Thanx.
>

Conditional formatting affects the entire cell, not individual
characters.

You could use an event macro. For instance, put this in your worksheet
code module (right-click the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTargets As Range
Dim rArea As Range
Dim rCell As Range
Dim i As Long

Set rTargets = Intersect(Target, Range("A1,C1"))
If Not rTargets Is Nothing Then
For Each rArea In rTargets.Areas
For Each rCell In rArea
With rCell
.Font.ColorIndex = 16 'all grey
If .Text Like "*#*" Then
For i = 1 To Len(.Text)
Select Case Mid(.Text, i, 1)
Case "0" To "9"
.Characters(i).Font.ColorIndex = 1 'black
Case ".", ","
If i > 1 And i < Len(.Text) Then
If Mid(.Text, i - 1, 3) Like "#?#" Then _
.Characters(i, 1).Font.ColorIndex = 1
End If
Case Else
'do nothing
End Select
Next i
End If
End With
Next rCell
Next rArea
End If
End Sub



In article <(E-Mail Removed)>,
"J.W. Aldridge" <(E-Mail Removed)> wrote:

> HI.
>
> I need to know how to set a conditional format that would font all
> numbers black but any letters gray within a cell.
>
> Thanx.

Correction:

Change

Case "0" To "9"
.Characters(i).Font.ColorIndex = 1 'black

to

Case "0" To "9"
.Characters(i, 1).Font.ColorIndex = 1 'black


In article <jemcgimpsey-(E-Mail Removed)>,
JE McGimpsey <(E-Mail Removed)> wrote:

> Conditional formatting affects the entire cell, not individual
> characters.
>
> You could use an event macro. For instance, put this in your worksheet
> code module (right-click the worksheet tab and choose View Code):
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim rTargets As Range
> Dim rArea As Range
> Dim rCell As Range
> Dim i As Long
>
> Set rTargets = Intersect(Target, Range("A1,C1"))
> If Not rTargets Is Nothing Then
> For Each rArea In rTargets.Areas
> For Each rCell In rArea
> With rCell
> .Font.ColorIndex = 16 'all grey
> If .Text Like "*#*" Then
> For i = 1 To Len(.Text)
> Select Case Mid(.Text, i, 1)
> Case "0" To "9"
> .Characters(i).Font.ColorIndex = 1 'black
> Case ".", ","
> If i > 1 And i < Len(.Text) Then
> If Mid(.Text, i - 1, 3) Like "#?#" Then _
> .Characters(i, 1).Font.ColorIndex = 1
> End If
> Case Else
> 'do nothing
> End Select
> Next i
> End If
> End With
> Next rCell
> Next rArea
> End If
> End Sub
>
>
>
> In article <(E-Mail Removed)>,
> "J.W. Aldridge" <(E-Mail Removed)> wrote:
>
> > HI.
> >
> > I need to know how to set a conditional format that would font all
> > numbers black but any letters gray within a cell.
> >
> > Thanx.