Formatting portions of cells

[Guest]

I have some code which uses ActiveCell.characters(##,##) type approach to
format the font of specific characters. I have had trouble though when the
code adds text to the cell and then does its coloring. Let me explain.

Say you have a cell and you are going to write 5 sentences in it via VB.
Say each of the 5 was going to be a different color. My existing code only
seems to get the first and last colors correct. Intermediate cells adopt the
formatting of the first sentence. For some reason, the formatting applied to
the intermediate sentences is getting lost.

I don't understand this but wonder if you have to be really careful with the
insert point. When I say .formula=.formula & "new text" for example, does
this approach end up erasing some of the previous formatting done to the cell?

 

[Guest]

Post the code used for the cell. It is difficult to analyze a narrative.

 

[Guest]

Alright, you asked for it A quick set of notes. I build up the string
strCurrentDateString. While doing so at each step I add the new text to the
..formula for the particular cell. Before doing it, I store the length of the
old formula, for use later in the character formatting code.
rngFilteredTasks is essentially a flat file database dumped on another
worksheet and manipulated to obtain the data. booWorkday is a function to
tell me if a given day of the week is a workday. Note: this "excel" code is
actually running in a Microsoft Project module.

strCurrentDateString = ""
With xlSheetCalendar.Cells(intRow, intCol)
If Not rngFilteredTasks Is Nothing Then
intTaskMax = rngFilteredTasks.Rows.Count
For intTask = 1 To intTaskMax
If booWorkday(rngFilteredTasks.Cells(intTask, 1),
datCurrent) Then
intStart = Len(strCurrentDateString)
strCurrentDateString = strCurrentDateString & " " &
rngFilteredTasks.Cells(intTask, 2)
intEnd = Len(strCurrentDateString)
.Formula = strCurrentDateString
With .Characters(Start:=intStart, Length:=intEnd -
intStart + 1).Font
.Underline = rngFilteredTasks.Cells(intTask, 6)
Select Case rngFilteredTasks(intTask, 5)
Case "A"
.ColorIndex = 53
Case "B"
.ColorIndex = 10
Case Else
.ColorIndex = 0
End Select
End With
strCurrentDateString = strCurrentDateString & vbLf &
vbLf
End If
Next
End If
End With