• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

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
0
Cook09
Asked:
Cook09
  • 5
  • 4
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Cook09Author Commented:
Appreciate the extra information on InStr.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now