software

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
JGquot;Stanleyquot; wrote:

gt; I have a function that does some calculations and returns some text. Now if
gt; the text is one thing I want to change the text color to red. If it is
gt; something else, change it to green, else leave it as is. I have some psuedo
gt; code below to sort of show what I am looking for. My problem is how to
gt; reference the cell that this function is being called from.
gt;
gt; Function CalculateSchedule(budget As String, actual As String) As String
gt; 'do calculations
gt;
gt; if behind text=red
gt; elseif ahead text=green
gt; else return text with no color
gt;
gt; End Function

My problem with that is that each cell could be quot;1 hour behindquot; or quot;23 hours
behindquot; 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 quot;On Schedulequot; but I need the other two colors for behind
and ahead.

I have tried this:

Application.ActiveCell.Font.Color = RGB(0, 255, 0) 'quot;greenquot;
CalculateSchedule = diff amp; quot; hours aheadquot;
Exit Function

But I never get the font to change.

quot;pinmasterquot; wrote:

gt; What you need is conditional formatting, select the cell or cells you want to
gt; format then go to Format/Conditional Formatting.
gt; condition 1: Cell value is: equal to: behind
gt; then click on Format and format as desired, click on Add to add the second
gt; condition
gt; condition 2: Cell value is: equal to: ahead
gt; again format as desired.
gt;
gt; HTH
gt; JG
gt;
gt;
gt; quot;Stanleyquot; wrote:
gt;
gt; gt; I have a function that does some calculations and returns some text. Now if
gt; gt; the text is one thing I want to change the text color to red. If it is
gt; gt; something else, change it to green, else leave it as is. I have some psuedo
gt; gt; code below to sort of show what I am looking for. My problem is how to
gt; gt; reference the cell that this function is being called from.
gt; gt;
gt; gt; Function CalculateSchedule(budget As String, actual As String) As String
gt; gt; 'do calculations
gt; gt;
gt; gt; if behind text=red
gt; gt; elseif ahead text=green
gt; gt; else return text with no color
gt; gt;
gt; gt; End Function

Use conditional formatting

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

Biff

quot;Stanleyquot; gt; wrote in message
...
gt; My problem with that is that each cell could be quot;1 hour behindquot; or quot;23
gt; hours
gt; behindquot; or any other number so I cannot match the whole string to make
gt; conditional format work. Same goes for ones that are ahead. The only thing
gt; I
gt; could match would be quot;On Schedulequot; but I need the other two colors for
gt; behind
gt; and ahead.
gt;
gt; I have tried this:
gt;
gt; Application.ActiveCell.Font.Color = RGB(0, 255, 0) 'quot;greenquot;
gt; CalculateSchedule = diff amp; quot; hours aheadquot;
gt; Exit Function
gt;
gt; But I never get the font to change.
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt;gt; What you need is conditional formatting, select the cell or cells you
gt;gt; want to
gt;gt; format then go to Format/Conditional Formatting.
gt;gt; condition 1: Cell value is: equal to: behind
gt;gt; then click on Format and format as desired, click on Add to add the
gt;gt; second
gt;gt; condition
gt;gt; condition 2: Cell value is: equal to: ahead
gt;gt; again format as desired.
gt;gt;
gt;gt; HTH
gt;gt; JG
gt;gt;
gt;gt;
gt;gt; quot;Stanleyquot; wrote:
gt;gt;
gt;gt; gt; I have a function that does some calculations and returns some text.
gt;gt; gt; Now if
gt;gt; gt; the text is one thing I want to change the text color to red. If it is
gt;gt; gt; something else, change it to green, else leave it as is. I have some
gt;gt; gt; psuedo
gt;gt; gt; code below to sort of show what I am looking for. My problem is how to
gt;gt; gt; reference the cell that this function is being called from.
gt;gt; gt;
gt;gt; gt; Function CalculateSchedule(budget As String, actual As String) As
gt;gt; gt; String
gt;gt; gt; 'do calculations
gt;gt; gt;
gt;gt; gt; if behind text=red
gt;gt; gt; elseif ahead text=green
gt;gt; gt; else return text with no color
gt;gt; gt;
gt;gt; gt; 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

quot;Biffquot; wrote:

gt; Use conditional formatting
gt;
gt; Select the cell(s) in question (I'll assume cell A1)
gt; Conditional formatting
gt; Condition 1
gt; Formula is: =SEARCH(quot;behindquot;,A1)
gt; Set the font color to RED
gt; Condition 2
gt; Formula is: =SEARCH(quot;aheadquot;,A1)
gt; Set the font color to GREEN
gt; OK out
gt;
gt; Biff
gt;
gt; quot;Stanleyquot; gt; wrote in message
gt; ...
gt; gt; My problem with that is that each cell could be quot;1 hour behindquot; or quot;23
gt; gt; hours
gt; gt; behindquot; or any other number so I cannot match the whole string to make
gt; gt; conditional format work. Same goes for ones that are ahead. The only thing
gt; gt; I
gt; gt; could match would be quot;On Schedulequot; but I need the other two colors for
gt; gt; behind
gt; gt; and ahead.
gt; gt;
gt; gt; I have tried this:
gt; gt;
gt; gt; Application.ActiveCell.Font.Color = RGB(0, 255, 0) 'quot;greenquot;
gt; gt; CalculateSchedule = diff amp; quot; hours aheadquot;
gt; gt; Exit Function
gt; gt;
gt; gt; But I never get the font to change.
gt; gt;
gt; gt; quot;pinmasterquot; wrote:
gt; gt;
gt; gt;gt; What you need is conditional formatting, select the cell or cells you
gt; gt;gt; want to
gt; gt;gt; format then go to Format/Conditional Formatting.
gt; gt;gt; condition 1: Cell value is: equal to: behind
gt; gt;gt; then click on Format and format as desired, click on Add to add the
gt; gt;gt; second
gt; gt;gt; condition
gt; gt;gt; condition 2: Cell value is: equal to: ahead
gt; gt;gt; again format as desired.
gt; gt;gt;
gt; gt;gt; HTH
gt; gt;gt; JG
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Stanleyquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; I have a function that does some calculations and returns some text.
gt; gt;gt; gt; Now if
gt; gt;gt; gt; the text is one thing I want to change the text color to red. If it is
gt; gt;gt; gt; something else, change it to green, else leave it as is. I have some
gt; gt;gt; gt; psuedo
gt; gt;gt; gt; code below to sort of show what I am looking for. My problem is how to
gt; gt;gt; gt; reference the cell that this function is being called from.
gt; gt;gt; gt;
gt; gt;gt; gt; Function CalculateSchedule(budget As String, actual As String) As
gt; gt;gt; gt; String
gt; gt;gt; gt; 'do calculations
gt; gt;gt; gt;
gt; gt;gt; gt; if behind text=red
gt; gt;gt; gt; elseif ahead text=green
gt; gt;gt; gt; else return text with no color
gt; gt;gt; gt;
gt; gt;gt; gt; End Function
gt;
gt;
gt;