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

I've attached a worksheet that has a before and after scenario. It seems that whatever code is used to create a LF (Chr 10), and copying the text to the next line, it loses the formatting (green = green in the color green, red = red in the color red).

Is there a way in VBA to take the first tab rows 7 and 11, and make it look like the second tab, rows 7 and 11
Report-Color.xlsx
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
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

Open in new window

0
Rgonzo1971Commented:
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

Open in new window

Regards
0
Cook09Author Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Cook09Author Commented:
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

Open 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?
0
Rgonzo1971Commented:
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
0
Cook09Author Commented:
Can you please explain the formatting:
 
InStr(3, ActiveCell.Text, " red ", vbTextCompare)

Open 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.
0
Rgonzo1971Commented:
3 is the starting point of the search
in "abcd"  search since c
0
Cook09Author Commented:
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?
0
Rgonzo1971Commented:
InStr(1, ActiveCell.Text, " cd ", vbTextCompare)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
Appreciate the extra information on InStr.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.