On Jun 27, 6:27*pm, "RJQ...@gmail.com" <RJQ...@gmail.com> wrote:
> On Jun 27, 10:59*am, "RJQ...@gmail.com" <RJQ...@gmail.com> 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? *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?
Similar Threads
