Function calls a Sub to change the cell font color

[grahamd]

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

 

[Niek Otten]

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

 

[Niek Otten]

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[graham d]

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

 

[Peter T]

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