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
784 Views

Similar Articles

[PageSpeed] 21

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:

Stock Split info-where is the info
I am in the wonderful process of starting my tax filing. If I have a stock buy entry for 100 shares of XYZ company at $20, the amount is $2000.GOOD. Now,if I record a 2-1 split, it will show 200 shares, at $10s, amount $2000. My questions is this, #1. how can I see what the stock split info was ? It seems there is no way to see it was a 2-1 ( this scenario is easy..but what if the stock splits 10 times over 10 years). I want to know, because I am getting lost over checking my 1099 against MSmoney. Any help would be good, thanks. The only way I have found to see this is to go into Updat...

Plain text #2
In replying to any incoming email message (no matter the format), how does one set the default to reply in plain text? I know that it can be done by individual email, but can it be set for all replies? TIA ...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

info
clik here for money http://kriskraft.site.voila.fr/ and GO for cash !!! see you soon chris ...

An unauthorized change has been made to windows
I've had this problem for a while and do not know how to fix it Here are the diagnostics Diagnostic Report (1.9.0011.0): ----------------------------------------- WGA Data--> Validation Status: Genuine Validation Code: 0 Cached Validation Code: N/A, hr = 0x80070426 Windows Product Key: *****-*****-KY9KP-RFHK9-B88K4 Windows Product Key Hash: J09uNHjCFdyr66hAVOmvEqN8lic= Windows Product ID: 89578-OEM-7318216-39793 Windows Product ID Type: 3 Windows License Type: OEM System Builder Windows OS version: 6.0.6000.2.00010300.0.0.003 ID: {DB436184-56A2-47D5-B91D-5E7F84BC7B...

Stop running sum(Over All) when customer change
Dear All I hv report based on tblTransaction which contains many customers with their transactions by date wise. I grouped report on CustomerID sub Group by Fromat(trnDate,”MMM-YY”) to get monthly transactions for each customer. I made calculated field to obtained closing balance with running sum over Group. I want to make the Running sum – over all but when customers is changing it must stop and start again, by default it will give running sum of all the customer, exactly this I don’t want. Any idea to stopped or make running sum based on customer when month is changing it ...

Changing Data format
I would be willing to do this either in Excel or Access, I am not sure of the preferred method I have data that starts out as below: MAIN NUM COMMON NUM A 0001 B 0001 C 0001 D 0001 A 0003 B 0003 A 0005 B 0005 C 0005 I need to change the way the data appears on the spreadsheet to the following: MAIN NUM COMMON NUM NUM 1 NUM 2 NUM 3 NUM 4 A 0001 A B C D B 0001 ...

Changing Addresses
I am using Outlook 2002. The situation I have is that I've got several typos in some of the email addresses that Outlook uses. I've noticed this in the past and I've made changes in the corresponding Contact entry. However, when I start typing the address in the To: field I get a drop down list of addresses to change and the typo version is still there and the corrected one is not. Can someone tell me how to find and delete the entries of the pop up list? Thanks "Jeff Harbin" <prez1210@sbcglobal.net> wrote in message news:4Hnyh.3266$gj4.53@newssvr14.news....

"**One or more line items contain warnings or error"
Hi! I have a VAR who has a concern regarding the "generic"-ness of this error:"**One or more line items contain warnings or error" on any Edit List in the Distributions Series. If there is a way to make this more specific, it would be great. It's rather weird why the system is not capable of erroring out in detail when there are fields that contain error values. My VAR’s concern is that why doesn’t GP give a very detailed error message considering that it already has a repository of errors? We have the table that contains the error code and somewhere in the...

Problems with retrieving info from my bank
I am hoping somebody might have some suggestions. I just replaced my previous computer and needed to reload MS Money onto my new one. Below is my predicament. Money Plus Premium Version 17.0.125.1415 Windows Vista Home Premium 64 I am trying to download statements from my Financial Institution (City County Credit Union). When asked if I want to download the file, I clicked on the download OFX as I have done for many years. I click "open" and it starts a program called Microsoft Office Accounting 2008. This is a brand new Computer and Office Ultimate 2007 has been inst...

outlook(?) changes email address upon sending
I have had trouble sending email to persons who have "netscape" as their email address. Somewhere in the process of sending the message, the word "netscape" is changed to "netescape" and the message is returned to me. This happens constantely. Any ideas? A bug? ...

Combo Box Text Alignment
Is it possible to tweek the text alignment in a combo box other then the standard "Left-Right-Center" preset options ? Thanks - George What specifically are you looking for? Assuming you use a query as the RowSource, you've always got the option of concatenating spaces in front (or after) a field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "George" <George@discussions.microsoft.com> wrote in message news:E1366215-05A0-45BB-A27B-272A47458FC8@microsoft.com... > Is it possible to tweek the text alignment in a ...

A full page of email text shrinks to one inch or less w/ new batte
I was answering emails and everyhting was fine. The batteries in my wireless mouse died. I replaced them and went back to answering emails. When I clicked the "forward" or "reply" button , a full page text in an email shrank to about one inch. Changing the font size did not correct the problem. HELP it's your zoom setting. Hold ctrl and roll the mouse wheel to increase. Depending on the mouse, you could have accidently pressed a mouse button and changed it when you changed the batteries. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.o...

How do capture the first three characters from one cell to another cell
"AGC4039" is in cell 1. I want cell 2 to grab the first thre characters from cell 1 so it will display "AGC" Anybody know if excel can do this? Thanks for your help! Ale -- ExcelAle ----------------------------------------------------------------------- ExcelAlex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555 View this thread: http://www.excelforum.com/showthread.php?threadid=27129 =LEFT(cell1,3) works dandy -- jared ----------------------------------------------------------------------- jaredh's Profile: http://www.excelfor...

Compare 1 cell to column of cells returning adjacent cells info?
I want to compare the value in one cell to another column of cells in another work sheet and once the matching value is found I wan to return a different cells value that is on the same line as the matching cell. Example Sheet 1 value is 34 in B6, so I want it to search Sheet 3 colum J for the value 34 which is found on row 351. When a match is found the value returned needs to be from colum O so this would be O351. Does anybody know how to setup this function? You are describing the VLOOKUP() function =VLOOKUP(B6,'Sheet 3'!$J$1:$0$500,6,0) "Mr. Fine"...

Adding total dollars based on specific data from another column
I have a cell I'm trying to calculate the total dollars based on specific criteria from a column of data. Example, column A has 25 cells with data like A, B, C, D, E, F, and a second column with dollar amounts associated with each of these outcomes, i.e. A=$5, B=$2, C=$9 etc. I want to create a formula in a different cell that tells me "if cell = A for $5 then what is the total dollar amount. A "if then" scenario....Any help???? -- Espo Hi! Maybe this: =SUMIF(A1:A25,"A",B1:B25) Biff "Espo" <Espo@discussions.microsoft.com> wrote in messa...

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...

how can I duplicate or copy a workbook then divide selected cells.
I must design a workbook which can be duplicated or copied to another workbook, as it is copied it must be divided to a rate which can be stored in a worksheet of the primary workbook... Is like when you want to calculate from Dolars to Euros. Hi use a formula such as ='[otherbook.xls]sheet1'!A1/$A$1 where A1 stores your rate -- Regards Frank Kabel Frankfurt, Germany "macros excel... duplication and calculat" <macros excel... duplication and calculat@discussions.microsoft.com> schrieb im Newsbeitrag news:149D60DB-D759-40B0-BAE6-8A72909395E1@microsoft.com... > ...

Add new row to table with only a few fields changing
We have a database table (in Access 97) which needs a new row added each month but only a handful of the fields change regularly, the rest almost always stay the same. What is the easiest way to ask for a few inputs from the user and automatically update the table with those new values but everything else copying over from the last row? Thanks Eric See: Assign default values from the last record at: http://allenbrowne.com/ser-24.html It's basically copy'n'paste code that duplicates all the fields from the previous record as soon as you start typing into a new reco...

How do I change from the default primary personal identification s
I am trying to use one of the other personal identification sets that are options but I cannot find a way to use any of the them except the primary set. How do I choose? Hi susieq- This is another one of those "make it so obviously simple that it becomes obscure" features... Go to Edit>Personal Information, select the set of info you want to use from the drop-down, then Click the Update button at the bottom of the dialog box. HTH |:>) "susieq" wrote: > I am trying to use one of the other personal identification sets that are > options but I cannot fi...

Payroll Report Info in 7.5
Our current 'Direct Deposit Statement of Earnings' report prints Available Sick Time on the report: 1. Which table and field is used for this data? 2. What is the best way to set all available sick time to zero for all employees and never accrue? Thanks UPR00100 is the table. You'll see fields for vacation and sick time available. The answer to resetting the balances depend upon if you are using HR Attendance to accrue or Payroll. If you're using Payroll, you can use a tool like SQL Server Query Analyzer to update the Sick Time Available field. If you're using At...

Table cells.
Hello to all my good friends out there. I have created a table with a size of 3.6cm high by 20cm wide. The table itself is 4 cells by 4 cells. If I decide to play about with the size of the individual cells making them higher, wider or shorter etc but then decide that I want them equally distributed in width and/or height again, is there easy way to do this. I cannot seem to do this without having to recreate a new table. The overall table size obviously must stay the same. Your help is and always has been very much appreciated. thanking you in anticipation. <-><-><->&...

message larger than current system limit -can i change the limit?
"...message is larger than the current system limit..." Can i change the limit to receive larger emails? I have construction documents/plans that require more space. It is awkward to ask a client to split it up and is burdensome to them. In article <97BF09CF-8C70-40D0-A74F-F4EA14F98A43@microsoft.com>, Shane@discussions.microsoft.com says... > > "...message is larger than the current system limit..." Can i change the > limit to receive larger emails? I have construction documents/plans that > require more space. It is awkward to ask a cli...

Changing page orientation on a multiple page doc
I just imported my newsletter into publish thinking it would look more professional in pub. I have the second to last page the calendar and print it in landscape while the rest of the doc prints in portrate. I can't get this to happen it changes the orientation of the whole doc. It wasn't a problem in Word or the other programs, they change page set up from this point forward or this page only etc ??? What's up with Publisher? I could get away with 2 doc's except the web version of the newsletter would not be the "print version", it would actually be 2 publis...

Portfolio Info Bar
The narrow info bar at the bottom of the portfolio screen has 3 places for such info as currect DJ Industrials index, S&P index, etc. Information on any of the indexes reported here is not correct and does not update itself to current - correct - information. All of the index info is stuck on a level of more than 1/2 year ago - maybe longer. Does anyone know if there is a fix for this? I am using Money 2004 small business. ...