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