Further Info on changing text in one cell to to criteria of another.

I have a workbook that charts the progress of my clients. One of m
cells automatically determines their age, and when they become olde
than six, I need another cell's text to automatically change to "AO
(standing for "Aged Out" of my program). I cannot us the functio
=IF(A1>6,"AO"," ") because I already have the clients status imputed i
that cell. I need it to be a type of conditional formatting or VB cod
so that it will automatically change once the reach 6. I have attache
an example of my workbook. It has a VB code that changes the row colo
based on Status. The Age row is CF'd to turn purple and BOLD when th
age reaches or exceeds six. I need the status row to automaticall
change from whatever text is in it to "AO" once age is greater than o
equal to six.  I will appreciate any imput that anyone has

                 Attachment filename: test.xls                 
Download attachment: http://www.excelforum.com/attachment.php?postid=64767
--
Message posted from http://www.ExcelForum.com

0
8/13/2004 3:10:46 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1138 Views

Similar Articles

[PageSpeed] 8

Conditional formatting can't change the contents of a cell. You could 
use code that runs when the workbook opens. For example, the following 
could would be stored on the ThisWorkbook module sheet:

Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Sheets("ECSS Client Status")
   Dim r As Long
   r = ws.UsedRange.Rows.Count
     For Each c In ws.Range(Cells(1, 8), Cells(r, 8))
       If c.Value > 6 Then
         c.Offset(0, -1).Value = "AO"
       End If
     Next c
End Sub

hoy13 < wrote:
> I have a workbook that charts the progress of my clients. One of my
> cells automatically determines their age, and when they become older
> than six, I need another cell's text to automatically change to "AO"
> (standing for "Aged Out" of my program). I cannot us the function
> =IF(A1>6,"AO"," ") because I already have the clients status imputed in
> that cell. I need it to be a type of conditional formatting or VB code
> so that it will automatically change once the reach 6. I have attached
> an example of my workbook. It has a VB code that changes the row color
> based on Status. The Age row is CF'd to turn purple and BOLD when the
> age reaches or exceeds six. I need the status row to automatically
> change from whatever text is in it to "AO" once age is greater than or
> equal to six.  I will appreciate any imput that anyone has.
> 
>                  Attachment filename: test.xls                  
> Download attachment: http://www.excelforum.com/attachment.php?postid=647671
> ---
> Message posted from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/13/2004 11:32:16 PM
This code works, however, I must go into VBA and run it every time that
I want it to run. How do I make it run automatically?


---
Message posted from http://www.ExcelForum.com/

0
8/18/2004 1:33:42 PM
Debra's suggestion was to put the code in the ThisWorkbook module.

She named the procedure Workbook_Open.  By using that name, the procedure will
run each time you open the workbook.

But I think that there's a slight problem it:

Change this line:
For Each c In ws.Range(Cells(1, 8), Cells(r, 8))
to
For Each c In ws.Range(ws.Cells(1, 8), ws.Cells(r, 8))

The unqualified range (cells()) could be a problem.

If you want to run the code on demand, you could store it in a General module.  

Then give it a nice name by changing:
Private Sub Workbook_Open()
to
Sub AdjustValues()

Then you could hit alt-f8 and select the macro and click run.


"hoy13 <" wrote:
> 
> This code works, however, I must go into VBA and run it every time that
> I want it to run. How do I make it run automatically?
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/18/2004 7:36:59 PM
You were correct that the "For Each c In..." line was causing me som
problems whenever I opened the workbook.  Your correction worke
beutifully.  Thanks for all of the help

--
Message posted from http://www.ExcelForum.com

0
8/19/2004 7:35:16 PM
Reply:

Similar Artilces:

How do I switch text from capitals to normal?
If I type an email in all CAPS, is there a way to make it into regular form wthout re-typing whole email? Yes, but the method depends on the version of Outlook and the Editor used. chris verbeski wrote: > If I type an email in all CAPS, is there a way to make it into regular form > wthout re-typing whole email? For m, it works like this - select all the text, then hold down shift and tap the F3 key, that toggles the text between lower case, capitals and then capital first letter for every word. It doesnt' work everywhere but works in Word. "Bob I" ...

Pound (#) signs instead of text
I entered text in a cell that is formatted for text and wrap. When I hit enter it shows as pound signs. I've tried making the cell wider and taller but that didn't help. I used the format brush to copy the format down from the cell above...still doesn't work. If you select the cell, does the text show up in the formula bar? -- Gary''s Student "mommyo" wrote: > I entered text in a cell that is formatted for text and wrap. When I hit > enter it shows as pound signs. I've tried making the cell wider and taller > but that didn't help. ...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Drawing info from another source document
Excel can 'gather' and update info into a formula, and use hyperlinks, but I had a Dos program called LUCID that could not only gather the information, but you could travel back and forth between the pages by using the <+> or <->. Is there any shortcut like that in Excel? If by "pages" you mean "worksheets", use CTRL + Pageup or Pagedown to go to next or previous sheet. Gord Dibben Excel MVP On Fri, 1 Jul 2005 12:59:04 -0700, "denny" <denny@discussions.microsoft.com> wrote: >Excel can 'gather' and update info into a fo...

How do I change the activity preview pane?
For Phone and Task Preview forms, I have tried to add the Description field to the form, but I am not able to make the field span two columns. Does anyone know a way around this? We need a better way to quickly jump between activity information while we're on the phone with a contact. Should we be storing this type of information somewhere else? Our sales team is finding this a barrier and thinks MS CRM is nothing more than a glorified address book. They are pushing us to move to ACT, but I think it’s just a matter of using this system more efficiently. Thanks for your help/inp...

Counting empty cells within a range of cells #2
A sheet can have a variable range of adjacent cells each containing the value =1 The range defines the perimeter of, or surrounds a group of empty cells however outside the perimeter, the remaining cells on the sheet are empty as well. The task is to count the empty cells that are surrounded by the perimeter of 1�s: To do this manually, I use the @countif (range=0) function however it requires that I go into the worksheet and select the range manually. I would like to do this by formula or macro. The knowns are: 1) The location of the start/end cell of the range 2) That the next cell in ...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

changing Exchange primary windows account
How To change primary Windows Account in exchange 5.5 mailbox, I have 3000 mailboxes in exchange 5.5 server. I want to change the primary windows account of all the users. can anybody suggest the faster way to do it.does anybody hv any tool. http://support.microsoft.com/default.aspx?scid=kb;en-us;155414 Sri wrote: > How To change primary Windows Account in exchange 5.5 > mailbox, I have 3000 mailboxes in exchange 5.5 server. > I want to change the primary windows account of all the > users. > can anybody suggest the faster way to do it.does anybody > hv any tool. ...

Changing vat rate calculation in Money 2006
I have enabled VAT calculations on my transactions in my accounts and the tax rate has changed but I can't find a way to change it in Money 2006. This is a database that has been upgraded over many versions and I'm sure I've changed this before in another version. I can see the old rate in some reports I print but for the life of me I can't find a setting in 2006. Anyone know of the way to change the Vat Rate? or is this another new feature ;(( -- Boyd In microsoft.public.money, Boyd Tucker wrote: >I have enabled VAT calculations on my transactions in my accou...

Changing values in a value box
Hi all, I have a data set that looks like this: DATE A$ B$ A% B% mar-14 50 100 33 66 mar-21 100 100 50 50 mar-28 75 25 75 25 I want to chart the percentages by each date, but I want the "value" box to display the dollars. The only way I can think to trick the Excel chart is create 2 x-axises and hide the $ axis, but then the $ values don't match the height of the % columns. Is there a way to link the $ values to the % columns? Thanks. Josh, To chart only the percentages, select the Date column, and also the two % columns (drag throug...

Changing the Organization Name
I need to change my organization name in CRM, my license keys are registered under my Full Company Name and CRM was installed without the LLC. Note: The organization name created in the previous procedure (setting up CRM) must match the organization specified when your license key was obtained. When you do the install, the Org Name you specifiy needs to match the Org Name that was used for the License Keys. So, in your case, just install it w/o the LLC (assuming that's what your license keys are setup for). The v1.2 install shouldn't let you by the screen where you enter this ...

How do I apply borders one side at a time
In Publisher I am trying to have a border on the top, on the bottom, on the leftside, but NOT on the rightn side. Can I do this? How? Select the object, click the line icon, more lines (styles, in 2000 and below) select the right side, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "the dr mike" <the dr mike@discussions.microsoft.com> wrote in message news:50E30E0F-85FC-4283-893F-D72FC2FE60CD@microsoft.com... > In Publisher I am trying to have a border on the top, on the bottom, on the > leftside...

Display cell attributes/format
Is there any way of examining the cell attributes/formatting on a worksheet other than individually with Format | Cell? I would assume so but can't find it. Thanks Brian Tozer Not in versions after and including xl97. -- Regards, Tom Ogilvy Brian Tozer <briantoz@ihug.co.nz> wrote in message news:bs3261$duo$1@lust.ihug.co.nz... > Is there any way of examining the cell attributes/formatting on a worksheet > other than individually with Format | Cell? > I would assume so but can't find it. > > Thanks > Brian Tozer > > ...

How do I print multiple records to one publication page?
I want to merge records from a data source in access to publisher. I want 4 different records per page in postcard format. Each time I use the catalog merge feature it prints 1 record 4 times on the page. How can I fix this? You need to have one card on your screen for the merge to work. (Print preview will show the same entry on all four cards, its a bug). -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jagodie" <Jagodie@discussions.microsoft.com> wrote in message news:CF2C96BA-6A33-4AF0-9703-A692713A5EA5@micro...

Changing a custom field length
We have created a custom section and there is a field (in that custom section) whose length has to be changed from 100 to 500 of nvarchar datatype, but the interface does not allow more than 100 for nvarchar. I couldnt modify the format of textbox to textarea as it is disabled nor i can change the datatype to another datatype. can anybody please help me with this?? Affy Unfortunately you'll have to recreate the field, as you can neither increse the length of a text field nor change it's format after it's been created. Then there's a question of what to do with any existing...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

Printing one pg item (visio), only prints 2nd 1/2 of page. Help?
I have a one page flowchart in Visio 2003. It fits an 8 1/2 x 11 page on print preview screen. When printed, only get 2nd half of the flowchart on the page, as though it only started printing from that point. All settings seem normal, am able to print normally in Word. Opened other visio flowcharts that had printed normally 30 minutes ago, and now only getting 2nd half of page. There were some printing issus with Visio 2003. Have applied the latest patch to Visio 2003 and are you using the latest printer drivers? John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3...

Dynamic text
Hi, I would like to add a serial number to a publication I'm designing. Is there a way to get a different number (sequential or otherwise) on a publication? Thanks Brian W Mail merge, create a data file. Read the help files, it is the way to all knowledge. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message news:eFVmhdGUDHA.2008@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to add a serial number to a publication I'm designing. Is there > a way to get a different number...

Get/Change operation on table 'SY_Error_Messages_MSTR'
Hi all, We upgraded from GP version 9 to version 10 over the past weekend. I have a user who is entering Payables Transactions and she is receiving the following message when she tries to enter a duplicate PO#.... Unhandled database exception: A get/change operation on table 'SY_Error_Messages_MSTR' cannot find the table. Invalid object name 'SY01700' So based on the error, the incorrect message is appearing to the user. Can I rebuild the SY01700 table? Is this really the issue? Any help would be greatly appeciated! Thanks, Lori The SY01700 Table holds the error mess...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

help
I have some texts files, which i want to read in each line, and then write back each line to a new text file. So for example, I want to read in the 2 lines below: "C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8 "",0,0,0,318.592,83.04552 I store each line of data in the following vector: std::vector<CString> FileData; So FileData[0] =3D ""C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8" FileData[1] =3D """,0,0,0,318.592,83.04552" (the data is in the vector correctly) Then I go to write the data to a new text file... FILE ...

Limiting The Amount of Text
Hi, Is there a way I can limit the amount of Text I can enter into th cells of a selected column? For example, when listing on eBay, the description bar on eBay i limited to 40 characters. Since I list items on eBay with Excel, i would save me a lot of time if I were able to lock the cells in m "Description" column to 40 characters instead of going back an counting all the characters in those cells. Is this doable ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum....

Opening Text Files and Re-Saving Them
Hi. I am trying to write some code which will allow a user to select a folder and then will open all of the text files in that folder and re-save them as excel files. I have sorted out the code to enable the user to select the folder they wish to use, but am looking some assistance on the looping to enable each of the text files to be opened and re-saved. Can anyone suggest how I might do this. Thanks. If the text files are all the same, you could record a macro that Opens, saves as an excel file, and closes the file. Then that recorded macro could be modified to open...

Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...

why does excel add 0's to a cell?
when I add numbers to a cell, excel adds tree zero's to the number. Is this a bug in excel? How can I fix this problem? Check the number format for the cell. Format it as General. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kathryn" <Kathryn@discussions.microsoft.com> wrote in message news:174D91D7-B8A2-4058-9A80-C47D33D369E6@microsoft.com... > when I add numbers to a cell, excel adds tree zero's to the > number. Is this a > bug in excel? How can I fix this problem? Kathryn, If Chip's su...