Conditional format words vs. numbers

J

J.W. Aldridge

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.
 
D

Dave Peterson

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?)
 
B

Bob Phillips

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

JE McGimpsey

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
 
J

JE McGimpsey

Correction:

Change

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

to

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