cant edit cells from vba

[mic]

I wrote this function

Function Test(r as Range)

R(1,1).Font.Color=VbRed

End Function

Now when I call this function in a cell Formula

=Test(A1:C3)

Nothing changed, like the cell is in lock to changes, but when I call the function from UserForm its work.

Any idea

Thanks

[mikerickson]

UDF's (User Defined Functions) return values. They don't change the environment, like changing cell colors, cell size, sheet names, etc.
In some cases, you can squeek something through when the UDF is called from other VB routine, but Excel is very strict about this when the UDF is called from a spreadsheet formula.

To change a cell's color, use the formatting controls. Or Condiditonal Formatting might better suit your needs.

[mic]

UDF's (User Defined Functions) return values. They don't change the environment, like changing cell colors, cell size, sheet names, etc.
In some cases, you can squeek something through when the UDF is called from other VB routine, but Excel is very strict about this when the UDF is called from a spreadsheet formula.

To change a cell's color, use the formatting controls. Or Condiditonal Formatting might better suit your needs.


thanks for your answer.
I now understand the limitation.
so I guess I will stick with the GUI method

thanks again.