Can I change the format of numerical & specific word referenceswithin a cell?

[RJQMAN]

I am using the program to construct a document. When the document is
completed, each cell contains a paragraph. The program works well.
However, I would like to be able to go inside each cell and change the
format whenever there is a dollar value shown from standard format to
bold face 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 the cell, after constructing and pasting
in the cell, 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" bold face and underline, as well as
$550.00 bold face and underline, and "May 2009" bold face 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 the cell, 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 the cell and 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?

 

[RJQMAN]

I am using the program to construct a document.  When the document is
completed, each cell contains a paragraph.  The program works well.
However, I would like to be able to go inside each cell and change the
format whenever there is a dollar value shown from standard format to
bold face 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 the cell, after constructing and pasting
in the cell, 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" bold face and underline, as well as
$550.00 bold face and underline, and "May 2009" bold face 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 the cell, 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 the cell and 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 to bold face, and underline them. However the code only
finds the first usage of the word in the paragraph. For example, I
want to underline and make bold the 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 to bold, 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

 

[RJQMAN]

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?  Hereis
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?