Conditional format words vs. numbers

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
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.
 
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?)
 
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)
 
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
 
Correction:

Change

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

to

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