On Jun 27, 6:27*pm, " wrote:
On Jun 27, 10:59*am, " wrote:





I am using the program to construct a document. *When the document is
completed, eachcellcontains a paragraph. *The program works well.
However, I would like to be able to go inside eachcelland change the
format whenever there is a dollar value shown from standard format to
boldface and underline. *I would, ideally, also like to change the
format when I state a month (January - December) and when a year
appears (2009, 2010, etc).

For example, the sentence in thecell, after constructing and pasting
in thecell, could read;

"The rent for the month of April 2009 is $550.00 payable on the last
day of May 2009."

I would like to make "April 2009"boldface and underline, as well as
$550.00boldface and underline, and "May 2009"boldface and
underline.

When I started this project, I was doing this by having the program
count the characters in the sentences and adding them together, to
calculate the place to start the format change, sort of like this...

With ActiveCell.Characters(Start:=StartPoint1,
Length:=LengthUnderline1).Font
* * * * .Bold= True
* * * * .Underline = True
End With

This worked fine, and I used the concept countless times, calculating
the starting and ending points of the format change based on the
sentences constructed and inserted into the paragraph.

However, the options in constructing the paragraph have grown beyond
my wildest imagination, and there are now so many ways that the user
can construct multiple sentences within thecell, that the counting
the characters method is a daunting program challenge.

I would like, if possible, to simply find the words in the paragraph
which was constructed and placed in thecelland change the format
when those words appear. *The words would always be the name of a
month, and the numbers would always be dollars. *Visual Basic is fine,
as the program is based on macros.

Is there a way to do this that is less cumbersome than counting the
characters before and the length thereof?

I was able to find code written by another person on this group that
solved 90% of my problem - I can now search for the specific words,
change them toboldface, and underline them. *However the code only
finds the first usage of thewordin the paragraph. *For example, I
want to underline and makeboldthe year 2009 in the following
statement;

In April 2008 until April 2009.

The code will catch the 1st 2009 but not the 2nd one. *I am thinking
(always a risky thing to do). that if the code could somehow overlook
the text after it has been changed tobold, I could put the entry in
two or three times, and it would work, but I have no idea how to do
that. *Perphaps there is a better solution. *Can anyone help? *Here is
the code that works so far;

Public Sub UnderlineInRed2()
Dim myArr As Variant
Dim i As Integer
Dim foundCell As Range
Dim foundAddr As String
Dim startPos As Integer

Sheets("Edit Page").Select

myArr = Array("January", "February", "March", "April", "May", "2008",
"2009")
' I omitted the other months and years here for clarity

For i = 0 To UBound(myArr)
* * Set foundCell = Columns("F:G").Find(what:=myArr(i), _
* * * * LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
* * If Not foundCell Is Nothing Then
* * * * foundAddr = foundCell.Address
* * * * Do
* * * * * * With foundCell
* * * * * * * * startPos = InStr(UCase(.Text), UCase(myArr(i)))
* * * * * * * * With .Characters(startPos, 4).Font
* * * * * * * * * * .Underline = True
* * * * * * * * * * .Bold= True
* * * * * * * * End With
* * * * * * End With
* * * * * * Set foundCell = Columns("F:G").FindNext(After:=foundCell)
* * * * Loop While Not foundCell Is Nothing And _
* * * * * * * *foundCell.Address < foundAddr
* * End If
Next i
End Sub- Hide quoted text -

- Show quoted text -

oops - I wrote that wrong

It should have said that the code would catch the first "April" but
not the 2nd one.
Can anyone help?