Hi guys

I have written a function which returns a double but i want to call a
sub from within it which will set the font color of the cell

(in reality i want it to set the color of the ActiveCell)

Function foo(ByVal x as Double) as Double
..
..
if x 2 then
call setColorTo3()
foo = 123.456
else
call setColorTo1()
foo = 1.111
end if
..
..
End function


Sub setColorTo3()
Activecell.Select
With Selection.Font
.ColorIndex = 3
.Bold = True
End With
End Sub

Sub setColorTo1()
Activecell.Select
With Selection.Font
.ColorIndex = 1
.Bold = True
End With
End Sub

Not possible. A function cannot change anything in worksheets or Excel
setting; it can just replace its call with a result.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"grahamd" wrote in message
om...
Hi guys

I have written a function which returns a double but i want to call a
sub from within it which will set the font color of the cell

(in reality i want it to set the color of the ActiveCell)

Function foo(ByVal x as Double) as Double
.
.
if x 2 then
call setColorTo3()
foo = 123.456
else
call setColorTo1()
foo = 1.111
end if
.
.
End function


Sub setColorTo3()
Activecell.Select
With Selection.Font
.ColorIndex = 3
.Bold = True
End With
End Sub

Sub setColorTo1()
Activecell.Select
With Selection.Font
.ColorIndex = 1
.Bold = True
End With
End Sub

But of course you can use conditional formatting to change the color

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"grahamd" wrote in message
om...
Hi guys

I have written a function which returns a double but i want to call a
sub from within it which will set the font color of the cell

(in reality i want it to set the color of the ActiveCell)

Function foo(ByVal x as Double) as Double
.
.
if x 2 then
call setColorTo3()
foo = 123.456
else
call setColorTo1()
foo = 1.111
end if
.
.
End function


Sub setColorTo3()
Activecell.Select
With Selection.Font
.ColorIndex = 3
.Bold = True
End With
End Sub

Sub setColorTo1()
Activecell.Select
With Selection.Font
.ColorIndex = 1
.Bold = True
End With
End Sub

Hi Niek

You said: "But of course you can use conditional formatting to change
the color"


Can you elaborate a little more

I showed a simplified function foo() but in reality a condition is set
in the function which determines foo's return value



Subsequently applying a format to each cell based on the value foo
returned wont really do my job -

thanks anyway


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Graham,

There one or two ways of triggering a Sub from a UDF, I use for a variety of
purposes concerned with colour. Such as:
Formatting a range, customize palette, creating and displaying a swatch of a
few hundred colours. However these methods are not reliable for general
use, at least I have not been able to make them such.

For your purposes I would go with Niek Otten's suggestion and CF. If you
have a simple UDF you can probably adapt the formula to return a true/false
Condition. In the CF dialog change "Cell Value Is" to "Formula Is" and then
your adapted formula. You have three conditions to play with plus a fourth,
namely the cell format that is displayed if none of the conditions are true.

Alternatively refer your CF's to whatever is returned by your UDF's

Regards,
Peter

"graham d" wrote in message
...
Hi Niek

You said: "But of course you can use conditional formatting to change
the color"


Can you elaborate a little more

I showed a simplified function foo() but in reality a condition is set
in the function which determines foo's return value



Subsequently applying a format to each cell based on the value foo
returned wont really do my job -

thanks anyway


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!