I have a function in which I can calculate the result differently based on
some input parameters. I would like to visually know which calculation path
I am selecting by setting different background colors. I see the examples
and have checked through these postings but I cannot change the background
color of the cell. I have moved the sheet off to its own xls file and
totally simplified the code:

Function Test1()
Sheets("Sheet1").Range("A1").Interior.ColorIndex = 4
Test1 = 6
End Function

The Test1 = 6 is just so I can verify that the function is being executed by
changing the 6 to other numbers.

I have tried:
ActiveCell.Interior.ColorIndex
RGB color settings
Selecting a Range and setting it

I have Protected the sheet and set AllowFormatting

Is there something that I need to set somewhere?

Are you calling this function from a cell in a worksheet?

If yes, then that's the way excel works--UDF's return values to the cell. They
don't change formatting.

jack wrote:
>
> I have a function in which I can calculate the result differently based on
> some input parameters. I would like to visually know which calculation path
> I am selecting by setting different background colors. I see the examples
> and have checked through these postings but I cannot change the background
> color of the cell. I have moved the sheet off to its own xls file and
> totally simplified the code:
>
> Function Test1()
> Sheets("Sheet1").Range("A1").Interior.ColorIndex = 4
> Test1 = 6
> End Function
>
> The Test1 = 6 is just so I can verify that the function is being executed by
> changing the 6 to other numbers.
>
> I have tried:
> ActiveCell.Interior.ColorIndex
> RGB color settings
> Selecting a Range and setting it
>
> I have Protected the sheet and set AllowFormatting
>
> Is there something that I need to set somewhere?

--

Dave Peterson

Wonder where that would be stated? Now that you say so, I remember doing
something before that I had to put on a control button. That is not what I
need here. Thanks for your help Dave.


"Dave Peterson" wrote:

> Are you calling this function from a cell in a worksheet?
>
> If yes, then that's the way excel works--UDF's return values to the cell. They
> don't change formatting.
>
> jack wrote:
> >
> > I have a function in which I can calculate the result differently based on
> > some input parameters. I would like to visually know which calculation path
> > I am selecting by setting different background colors. I see the examples
> > and have checked through these postings but I cannot change the background
> > color of the cell. I have moved the sheet off to its own xls file and
> > totally simplified the code:
> >
> > Function Test1()
> > Sheets("Sheet1").Range("A1").Interior.ColorIndex = 4
> > Test1 = 6
> > End Function
> >
> > The Test1 = 6 is just so I can verify that the function is being executed by
> > changing the 6 to other numbers.
> >
> > I have tried:
> > ActiveCell.Interior.ColorIndex
> > RGB color settings
> > Selecting a Range and setting it
> >
> > I have Protected the sheet and set AllowFormatting
> >
> > Is there something that I need to set somewhere?
>
> --
>
> Dave Peterson
>