Formatting multi-line text

[Tim H.]

I have a process that reads through a list of dates and tasks on one sheet to
create a calendar summary view on a separate sheet to show all tasks
applicable for each date. One cell is used for each calendar day, with each
applicable task for that day listed on a separate line, via CR/LF. I would
like to be able to use text formatting to differentiate tasks that are
completed (strikethrough font), open (regular font), or priority tasks (bold).

The process currently appends tasks by simply setting the cell to the
current value appended by a CR/LF and the new text to be added:

c.value = c.value & chr(10) & strNewText

Unfortunately I have not been able to create logic to assign text formatting
as desired. I have attempted to set the font using the Characters property
for the applicable characters, but each time the text is appended all
contents of the cell are reset to the format of the first character of the
cell. Does anyone have any suggestions on how to get around this?

Thanks,
Tim

[Tom Ogilvy]

In your code, You would have to examine the cell and record the exsiting
formatting
then append the text, then reassign the old formatting plus any additional
formatting for the new text.

--
Regards,
Tom Ogilvy


"Tim H." wrote:

> I have a process that reads through a list of dates and tasks on one sheet to
> create a calendar summary view on a separate sheet to show all tasks
> applicable for each date. One cell is used for each calendar day, with each
> applicable task for that day listed on a separate line, via CR/LF. I would
> like to be able to use text formatting to differentiate tasks that are
> completed (strikethrough font), open (regular font), or priority tasks (bold).
>
> The process currently appends tasks by simply setting the cell to the
> current value appended by a CR/LF and the new text to be added:
>
> c.value = c.value & chr(10) & strNewText
>
> Unfortunately I have not been able to create logic to assign text formatting
> as desired. I have attempted to set the font using the Characters property
> for the applicable characters, but each time the text is appended all
> contents of the cell are reset to the format of the first character of the
> cell. Does anyone have any suggestions on how to get around this?
>
> Thanks,
> Tim

[Tim H.]

Thanks, Tom. I was afraid of that. My concern is how to be able to capture
the existing formatting since I could theoretically have a day (cell) with
several lines, each with alternating formatting (any combination of
strikethrough, regular and bold). When appending a subsequent entry I would
need to capture the formatting for each current line, append the new text,
and format the next line as necessary.

Tim

"Tom Ogilvy" wrote:

> In your code, You would have to examine the cell and record the exsiting
> formatting
> then append the text, then reassign the old formatting plus any additional
> formatting for the new text.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tim H." wrote:
>
> > I have a process that reads through a list of dates and tasks on one sheet to
> > create a calendar summary view on a separate sheet to show all tasks
> > applicable for each date. One cell is used for each calendar day, with each
> > applicable task for that day listed on a separate line, via CR/LF. I would
> > like to be able to use text formatting to differentiate tasks that are
> > completed (strikethrough font), open (regular font), or priority tasks (bold).
> >
> > The process currently appends tasks by simply setting the cell to the
> > current value appended by a CR/LF and the new text to be added:
> >
> > c.value = c.value & chr(10) & strNewText
> >
> > Unfortunately I have not been able to create logic to assign text formatting
> > as desired. I have attempted to set the font using the Characters property
> > for the applicable characters, but each time the text is appended all
> > contents of the cell are reset to the format of the first character of the
> > cell. Does anyone have any suggestions on how to get around this?
> >
> > Thanks,
> > Tim

[Tom Ogilvy]

create a user defined type with the attributes of interest.

then make a dynamic array of the same type as the UDT. Then redim it to the
length of as the string, and loop through the string, recording the
properties character by character using the character object.


Here is some pseudo code:

Public Type MyType
FontColor As Long
FontBold As Boolean
Fontname As String
FontStrikeThrough As Boolean
End Type
Sub ProcessCell()
Dim v() As MyType
Dim cell As Range
Dim oldLength As Long
Set cell = ActiveCell
oldLength = Len(cell)
For i = 1 To oldLength
With cell.Characters(i, 1).Font
v(i).FontBold = .Bold
v(i).Fontname = .Name
v(i).FontColor = .ColorIndex
v(i).FontStrikeThrough = .Strikethrough
End With
Next
' now concatenate the string, and then
' loop through it re-setting the values.
End Sub


--
Regards,
Tom Ogilvy


"Tim H." wrote:

> Thanks, Tom. I was afraid of that. My concern is how to be able to capture
> the existing formatting since I could theoretically have a day (cell) with
> several lines, each with alternating formatting (any combination of
> strikethrough, regular and bold). When appending a subsequent entry I would
> need to capture the formatting for each current line, append the new text,
> and format the next line as necessary.
>
> Tim
>
> "Tom Ogilvy" wrote:
>
> > In your code, You would have to examine the cell and record the exsiting
> > formatting
> > then append the text, then reassign the old formatting plus any additional
> > formatting for the new text.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Tim H." wrote:
> >
> > > I have a process that reads through a list of dates and tasks on one sheet to
> > > create a calendar summary view on a separate sheet to show all tasks
> > > applicable for each date. One cell is used for each calendar day, with each
> > > applicable task for that day listed on a separate line, via CR/LF. I would
> > > like to be able to use text formatting to differentiate tasks that are
> > > completed (strikethrough font), open (regular font), or priority tasks (bold).
> > >
> > > The process currently appends tasks by simply setting the cell to the
> > > current value appended by a CR/LF and the new text to be added:
> > >
> > > c.value = c.value & chr(10) & strNewText
> > >
> > > Unfortunately I have not been able to create logic to assign text formatting
> > > as desired. I have attempted to set the font using the Characters property
> > > for the applicable characters, but each time the text is appended all
> > > contents of the cell are reset to the format of the first character of the
> > > cell. Does anyone have any suggestions on how to get around this?
> > >
> > > Thanks,
> > > Tim