PDA

View Full Version : Solved: How to Format Font in Middle of String?



Cyberdude
05-27-2008, 01:26 PM
If I have a cell that contains the string: ?AAAZZZBBB?, then I can manually change the size and color of the substring ?ZZZ? by selecting those characters with my cursor, then clicking on the desired color in my color pallet and the font size up-down control icon: ?AAAZZZBBB?

Is there a way do perform substring font reformatting using VBA?

Bob Phillips
05-27-2008, 01:30 PM
With Activecell.Characters(4,3).Font
.ColorIndex=3
.Bold = True
End With

Cyberdude
05-27-2008, 02:28 PM
Very nice solution, XLD. Thanks! Here's what I came up with:

Sub ReformatSubstring()
Range("H96").Select
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "111999222"
With ActiveCell.Characters(4, 3).Font
.ColorIndex = 3
.Bold = True
End With
End Sub
One more question: when the string contains a series of digits that Excel interprets to be a number, I get one of those pesky "number stored as text" error notices . . . the kind that has a little red triangle in the upper left corner of the cell. Is there a way to suppress that error message? It will go away if I manually select "Ignore Error", but this isn't something I really want to do if I have a bunch of them on a sheet.

Bob Phillips
05-27-2008, 02:54 PM
There is an option in Tools>Options>Error Checking to turn off that error.

NukedWhale
05-27-2008, 04:54 PM
I'm trying to do a similar task, but (1) I need the code to work for all cells within a range and (2) the start position and number of characters that I'm reformatting within the substring changes.

Let's assume the range I'm reformatting is A1:A5 and I have the corresponding start position and number of characters stored in B1:B5 and C1:C5 respectively.

Thanks for any help, I'm new to VBA.

Bob Phillips
05-27-2008, 05:48 PM
Dim cell As Range

For Each cell In Range("A1:A5")

If cell.Value <> "" And cell.Offset(0, 1).Value <> "" And cell.Offset(0, 2).Value <> "" Then

With cell.Characters(cell.Offset(0, 1).Value, cell.Offset(0, 2).Value)

.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
Next cell

NukedWhale
05-28-2008, 07:53 AM
Thanks for the help xld, your code did exactly what I needed it to.

Cyberdude
05-28-2008, 01:19 PM
Thanx, XLD for the error notice suppression hint. I found that the following statement can be used in VBA to control whether the error is displayed or not:
Application.ErrorCheckingOptions.NumberAsText = False I thought someone else might be interested.

Sid

Bob Phillips
05-28-2008, 01:34 PM
That is just doing the same thing via VBA, it is the same system flag being set/unset in both cases.