Jonas,

No you cannot modify format from within a function, simply return a value.

Why is CF not sufficient. Is it because it only has 3 conditions? If so, you
could use event code liek this

Private Sub Worksheet_Change(By Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4
Case "c": Target.Interior.ColorIndex = 34
Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips

"Jonas Caspersson" wrote in message
...
Hi, i have a Excel question.

Is it possible to from within a user defined function (VBA) modify the
cells
properties, like .interior.color?

This would help since the built-in function 'Conditional formatting' is of
no help for my specific demands.

What i want is to let the user select a function called for instance
'FormatColor' from the insert function dialog box, and then choose an
refernence cell which contains the formats he wants to use.

Exampel

within VBA module:

Function FormatCell (Ref as Range)
Application.ActiveCell.Interior.Color = Ref.Interior.Color
End Function

The user then invokes the funktion via 'insert function - user defined
funtion - FormaCell'
- Picks the reference cell of chiose.

This does not work - I've tried... :o(
And by the way, Excel hangs!

(i've tried this with Excel XP)

Help, please

/Jonas Caspersson