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

[hoy13]

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

 

[Debra Dalgleish]

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]

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?

 

[Dave Peterson]

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]

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