I have a function that does some calculations and returns some text. Now if
the text is one thing I want to change the text color to red. If it is
something else, change it to green, else leave it as is. I have some psuedo
code below to sort of show what I am looking for. My problem is how to
reference the cell that this function is being called from.

Function CalculateSchedule(budget As String, actual As String) As String
'do calculations

if behind text=red
elseif ahead text=green
else return text with no color

End Function

What you need is conditional formatting, select the cell or cells you want to
format then go to Format/Conditional Formatting.
condition 1: Cell value is: equal to: behind
then click on Format and format as desired, click on Add to add the second
condition
condition 2: Cell value is: equal to: ahead
again format as desired.

HTH
JG


"Stanley" wrote:

I have a function that does some calculations and returns some text. Now if
the text is one thing I want to change the text color to red. If it is
something else, change it to green, else leave it as is. I have some psuedo
code below to sort of show what I am looking for. My problem is how to
reference the cell that this function is being called from.

Function CalculateSchedule(budget As String, actual As String) As String
'do calculations

if behind text=red
elseif ahead text=green
else return text with no color

End Function

My problem with that is that each cell could be "1 hour behind" or "23 hours
behind" or any other number so I cannot match the whole string to make
conditional format work. Same goes for ones that are ahead. The only thing I
could match would be "On Schedule" but I need the other two colors for behind
and ahead.

I have tried this:

Application.ActiveCell.Font.Color = RGB(0, 255, 0) '"green"
CalculateSchedule = diff & " hours ahead"
Exit Function

But I never get the font to change.

"pinmaster" wrote:

What you need is conditional formatting, select the cell or cells you want to
format then go to Format/Conditional Formatting.
condition 1: Cell value is: equal to: behind
then click on Format and format as desired, click on Add to add the second
condition
condition 2: Cell value is: equal to: ahead
again format as desired.

HTH
JG


"Stanley" wrote:

I have a function that does some calculations and returns some text. Now if
the text is one thing I want to change the text color to red. If it is
something else, change it to green, else leave it as is. I have some psuedo
code below to sort of show what I am looking for. My problem is how to
reference the cell that this function is being called from.

Function CalculateSchedule(budget As String, actual As String) As String
'do calculations

if behind text=red
elseif ahead text=green
else return text with no color

End Function

Use conditional formatting

Select the cell(s) in question (I'll assume cell A1)
Conditional formatting
Condition 1
Formula is: =SEARCH("behind",A1)
Set the font color to RED
Condition 2
Formula is: =SEARCH("ahead",A1)
Set the font color to GREEN
OK out

Biff

"Stanley" wrote in message
...
My problem with that is that each cell could be "1 hour behind" or "23
hours
behind" or any other number so I cannot match the whole string to make
conditional format work. Same goes for ones that are ahead. The only thing
I
could match would be "On Schedule" but I need the other two colors for
behind
and ahead.

I have tried this:

Application.ActiveCell.Font.Color = RGB(0, 255, 0) '"green"
CalculateSchedule = diff & " hours ahead"
Exit Function

But I never get the font to change.

"pinmaster" wrote:

What you need is conditional formatting, select the cell or cells you
want to
format then go to Format/Conditional Formatting.
condition 1: Cell value is: equal to: behind
then click on Format and format as desired, click on Add to add the
second
condition
condition 2: Cell value is: equal to: ahead
again format as desired.

HTH
JG


"Stanley" wrote:

I have a function that does some calculations and returns some text.
Now if
the text is one thing I want to change the text color to red. If it is
something else, change it to green, else leave it as is. I have some
psuedo
code below to sort of show what I am looking for. My problem is how to
reference the cell that this function is being called from.

Function CalculateSchedule(budget As String, actual As String) As
String
'do calculations

if behind text=red
elseif ahead text=green
else return text with no color

End Function

Biff that worked perfectly. Will definetly have to remeber the SEARCH bit as
I have several more ideas that can use something like that.

-Stanley

"Biff" wrote:

Use conditional formatting

Select the cell(s) in question (I'll assume cell A1)
Conditional formatting
Condition 1
Formula is: =SEARCH("behind",A1)
Set the font color to RED
Condition 2
Formula is: =SEARCH("ahead",A1)
Set the font color to GREEN
OK out

Biff

"Stanley" wrote in message
...
My problem with that is that each cell could be "1 hour behind" or "23
hours
behind" or any other number so I cannot match the whole string to make
conditional format work. Same goes for ones that are ahead. The only thing
I
could match would be "On Schedule" but I need the other two colors for
behind
and ahead.

I have tried this:

Application.ActiveCell.Font.Color = RGB(0, 255, 0) '"green"
CalculateSchedule = diff & " hours ahead"
Exit Function

But I never get the font to change.

"pinmaster" wrote:

What you need is conditional formatting, select the cell or cells you
want to
format then go to Format/Conditional Formatting.
condition 1: Cell value is: equal to: behind
then click on Format and format as desired, click on Add to add the
second
condition
condition 2: Cell value is: equal to: ahead
again format as desired.

HTH
JG


"Stanley" wrote:

I have a function that does some calculations and returns some text.
Now if
the text is one thing I want to change the text color to red. If it is
something else, change it to green, else leave it as is. I have some
psuedo
code below to sort of show what I am looking for. My problem is how to
reference the cell that this function is being called from.

Function CalculateSchedule(budget As String, actual As String) As
String
'do calculations

if behind text=red
elseif ahead text=green
else return text with no color

End Function