I've written an Excel function. I'd like for it to be able to change the
formatting of the cell that it's currently in depending on the what happens
in the function. I'd like the function to be able to control the font,
interior, and pattern. I don't want to use the conditional formating feature.

Functions return values. They can not modify other cells nor can they change
formatting when called from a sheet as a user defined function. If they are
called from code then they can do what they want but as a UDF they are
limited. Oddly enugh there are acouple of exceptions. They can change
comments in other cells and a couple of other weird ones (if I recall) but
not what you are looking to do.
--
HTH...

Jim Thomlinson


"Roger Starnes" wrote:

I've written an Excel function. I'd like for it to be able to change the
formatting of the cell that it's currently in depending on the what happens
in the function. I'd like the function to be able to control the font,
interior, and pattern. I don't want to use the conditional formating feature.

Roger

Functions cannot change formatting.

They can only return results to the cell in which they reside.

Take another approach.......like CF?

Or event code which can change formatting depending upon value returned by a
Function.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben Excel MVP

On Fri, 9 Dec 2005 15:05:02 -0800, "Roger Starnes" <Roger
wrote:

I've written an Excel function. I'd like for it to be able to change the
formatting of the cell that it's currently in depending on the what happens
in the function. I'd like the function to be able to control the font,
interior, and pattern. I don't want to use the conditional formating feature.