Format cell for different colored text...

Here's one way to multiformat same-cell characters

Tony,

Try this code, I think it does what you want.

Sub EditFont()
'To format font color for 12 digits to 4 black, 5 red, 3 black:
'First, format digits to be treated as characters
ActiveCell.Value = "'" & ActiveCell.Value
'Format all characters for black
With ActiveCell
.Font.ColorIndex = 3
'Format characters 5 thru 12 as red
.Characters(1, ActiveCell.Characters.Count - 8).Font.ColorIndex = 1
'Reformat characters 10 thru 12 back to black
.Characters(10, ActiveCell.Characters.Count - 3).Font.ColorIndex = 1
End With
End Sub

OK?

Tom Urtis

Re: Here's one way to multiformat same-cell characters

,

: I have a file with a single column that contains 12 digits. I would like to format the digits so that the first the first 4 digits are black, the next 5 digits are red and the last 3 digits are black. : Thanks, : Tony

Tom,

Thanks for the script, it works well. I would like to apply this to an existing column, any suggestion on how to make this work on existing text in a column without having to hot-key every cell?

Tony

Re: Here's one way to multiformat same-cell characters

One thing you can do is loop the procedure; looping is not my first preference generally, but it's useful in your case because you are formatting digits and not letters.

Select the first such cell you want to format this way, and run this modified procedure:

Sub EditFont()
Application.ScreenUpdating = False
Do Until ActiveCell.Value = ""
ActiveCell.Value = "'" & ActiveCell.Value
With ActiveCell
.Font.ColorIndex = 3
.Characters(1, ActiveCell.Characters.Count - 8).Font.ColorIndex = 1
.Characters(10, ActiveCell.Characters.Count - 3).Font.ColorIndex = 1
End With
ActiveCell.Offset(1, 0).Activate
Loop
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Is this what you want?

Tom Urtis

: Tony, : Try this code, I think it does what you want. : Sub EditFont() : 'To format font color for 12 digits to 4 black, 5 red, 3 black

Re: Here's one way to multiformat same-cell characters

Tom,

Great!!! Thanks for the help...

Tony