Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the format of numerical & specific word referenceswithin a cell?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the format of numerical & specific word referenceswithin a cell?
On Jun 27, 10:59*am, " wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the format of numerical & specific word referenceswithin a cell?
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change format for an individual word in a cell | Excel Discussion (Misc queries) | |||
How to change column format? numerical not alphabetical | Excel Discussion (Misc queries) | |||
how can I format a cell to change on a specific date | New Users to Excel | |||
convert a numerical currency into word format | Excel Worksheet Functions | |||
Change format of single word in cell | Excel Programming |