Add a Title and Linefeed to a worksheet row, and keep the character format

Quick and dirty....

Sub FormatTab()
    Sheets("Late & Missing-Desired").Select
    Rows("7:7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Late & Missing").Select
    Rows("7:7").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("Late & Missing-Desired").Select
    Rows("11:11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Late & Missing").Select
    Rows("11:11").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

End Sub

Select allOpen in new window

HI,

to copy a property in a part of a cell text you have to use Characters

Sub macro()
Dim arrColors() As Variant
strAddTxt = "Late & Missing" & Chr(10)
Set c = Range("A7")
dblLenTxt = Len(c)
ReDim arrColors(dblLenTxt - 1)
For Idx = 0 To dblLenTxt - 1
    arrColors(Idx) = c.Characters(Idx + 1, 1).Font.Color
Next
c.Value = strAddTxt & c.Value
c.Characters(1, Len(strAddTxt)).Font.Size = 11
For Idx = 0 To dblLenTxt - 1
    c.Characters(Idx + 1 + Len(strAddTxt), 1).Font.Color = arrColors(Idx)
Next
End Sub

Select allOpen in new window

Regards

The solution posted by Steve didn't work.  It only brought over the text below the line feed and not the individual headings

The one presented by Rgonzo1971 also didn't seem to work on the worksheet provided, but did on the original that I have.  There are a couple of questions:
1. How to have the code move from 7 to the end, which could be 50 to 70 rows, and look for those rows that have "The following", which would include the entire contents of A7 or A11, and run it only on those rows?  I've tried but the other rows expand to the same size as 7 and 11
 2. I know that we are looking at characters, but is there a way to search for the word "red" and apply the color red to the Font, and for the word Green and apply a green to it.?
3. Given the code, if I have to make additional color changes if needed, once these were made, how would I apply other formatting changes, such as bold, or maybe a different color somewhere?

Thanks,
Cook

I was able to come up with a solution that works.

Dim r As Long, g As Long, lastrow As Long

 For J = 6 To lastrow
             If Cells(J, "A").Value Like "The following *" Then
                 Range("A" & J) = "Misdelivered:" & Chr(10) & Range("A" & J)
                    Range(Cells(J, "A"), Cells(J, "I")).Select
                     With Selection
                       .Font.Bold = False
                       .Font.Italic = True
                       .Characters(1, 14).Font.Size = 11
                       .Characters(1, 14).Font.Italic = False
                       .Characters(1, 14).Font.Bold = True
                       .MergeCells = True
                       .WrapText = True
                       .RowHeight = 42
                    End With
                           
                    r = InStr(3, ActiveCell.Text, " red ", vbTextCompare)   'Added for red  7/16
                 If r > 0 Then ActiveCell.Characters(r, 5).Font.Color = vbRed
                        ActiveCell.Characters(r, 5).Font.Bold = True

                    g = InStr(3, ActiveCell.Text, "green", vbTextCompare)
                 If g > 0 Then ActiveCell.Characters(g, 5).Font.Color = RGB(0, 153, 0)
                  ActiveCell.Characters(g, 5).Font.Bold = True
            End If

Select allOpen in new window

But is there a way to make this a function, such that it would check all of the pages and if the specific word appeared, then formatting could be applied to it?  I tried to look at conditional formatting, but it seems to format everything inside a cell and not just one word or phrase....is that correct?

Conditional formatting is for the whole cell

Are you trying to make the changes in different sheets (pages in your comment)
then try

For Each sh In ActiveWorkbook.Worksheets
    sh.Activate
    ' Your Code
Next


Regards

Can you please explain the formatting:
 

InStr(3, ActiveCell.Text, " red ", vbTextCompare)

Select allOpen in new window

InStr(3 is a bit confusing as to what it actually does.  Most of the examples use 1, but I tried using a higher value, as it would format "Misdelivered", with Misdelivered as red and not format the word single word "red," later on in the cell text.  It was my understanding that the first value was a starting point (but from where). When a higher value, i.e. 10 was used, it would not format the single word "red," even though it was more than 10 words from the beginning of the text.  Why it took 3, I don't know.

Do you know exactly how this is supposed to work, and/or is there a better method to format a particular word, within a cell, even though the word may also be part of a larger word, prior to the single one? Fortunately, I could use a space before and after the word, but that doesn't seem to be a real solution with the InStr function.

3 is the starting point of the search
in "abcd"  search since c

If I happen to have abcd efgh ijcd klmn cd opqr and just wanted to make the standalone "cd" a different color, how would that be written within this function?

Appreciate the extra information on InStr.