Changing Text Format for Part of a Cell 1

[AGlazer]

Hi, everyone.

I have a cell that looks like this:

Title
Text1
Text2
Text3

There are hard returns between each line.

I want to write a macro to edit only the second through fourth lines of the cell and change the font size on it, but I can't figure out how to only address part of a cell with VBA. I can't put these line items in separate cells, either, as they have to be copied in and out of Excel in bulk. Any ideas?

Thanks,

Aaron

 

[VBAjedi]

Here you go. This function formats all text in a cell after the first hard return (Alt-Enter). You could tweak it if you don't want to go all the way to the end, but it sounds like you do. I left all of the font properties in there in case you want to change something besides the size, but you can delete any ones you don't need. I included a test sub that applies the function to cell B3. Just set up a loop to feed the appropriate cells to the function:

Sub TestFormat()
Dim c As Range
Dim x
Set c = Worksheets("Sheet1").Range("B3")
x = FormatPart(c)
End Sub

Function FormatPart(c As Range)
Dim x, y

y = c.Value
For x = 1 To Len(y)
   If Asc(Mid(y, x, 1)) = 10 Then
      With c.Characters(Start:=(x + 1), Length:=(Len(y) - x)).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
      End With
      Exit For
   End If
Next x
End Function

Let me know if this does it for you!


VBAjedi

 

[AGlazer]

VBAjedi,

You rock. That's perfect - thanks!

Aaron