MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Format cell for different colored text...


Posted by Tony Kirgis on December 10, 2001 5:46 AM

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


Posted by Tom Urtis on December 10, 2001 6:23 AM

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

Posted by Tony Kirgis on December 10, 2001 7:00 AM

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

Posted by Tom Urtis on December 10, 2001 7:49 AM

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

Posted by Tony Kirgis on December 10, 2001 9:09 AM

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

Tom,

Great!!! Thanks for the help...

Tony