Cell background color (interior color) setting not working

I am having a problem getting this to work and would appreciate your input.


In "Module1":


Function SetBackgroundColor(Parameter As Range) As String
Dim xlRange As Range

SetBackgroundColor = ""

If (TypeName(Application.Caller) = "Range") Then
Set xlRange = Application.Caller
xlRange.Interior.Color = ValueToRGB(CInt(Parameter.Value))
End If

End Function

Here "ValueToRGB" simply has a Select Case statement that outputs specific
RGB (long) values based on input. This function works well and has been
verified.

The problem is that "SetBackgroundColor" never changes the background color
of the target cell.

I've even tried such code as:

[A1].Interior.Color = ValueToRGB(1)


[A2].Interior.Color = ValueToRGB(2)


[A3].Interior.Color = ValueToRGB(3)

to no avail.

However, if the above three lines are place into a public Sub that is called
by hand (By executing the macro via ALT-F8 (Tools/Macro/Macros) all three
cells are colorized perfectly.

For some reason setting the interior color does not work from a user
function, regardless of whether the target is the function within which the
function is being called or an entirely different (even hard-coded, rather
than passed or calculated) cell.

Any ideas?

Thanks,

-Martin

That is correct. A function used in a worksheet can not alter the excel
environment. It can only return a value to the cell where it is located.

--
Regards,
Tom Ogilvy

"Martin E." <[email protected]> wrote in message
news:[email protected]...
> I am having a problem getting this to work and would appreciate your
input.
>
>
> In "Module1":
>
>
> Function SetBackgroundColor(Parameter As Range) As String
> Dim xlRange As Range
>
> SetBackgroundColor = ""
>
> If (TypeName(Application.Caller) = "Range") Then
> Set xlRange = Application.Caller
> xlRange.Interior.Color = ValueToRGB(CInt(Parameter.Value))
> End If
>
> End Function
>
> Here "ValueToRGB" simply has a Select Case statement that outputs specific
> RGB (long) values based on input. This function works well and has been
> verified.
>
> The problem is that "SetBackgroundColor" never changes the background
color
> of the target cell.
>
> I've even tried such code as:
>
> [A1].Interior.Color = ValueToRGB(1)
>
>
> [A2].Interior.Color = ValueToRGB(2)
>
>
> [A3].Interior.Color = ValueToRGB(3)
>
> to no avail.
>
> However, if the above three lines are place into a public Sub that is
called
> by hand (By executing the macro via ALT-F8 (Tools/Macro/Macros) all three
> cells are colorized perfectly.
>
> For some reason setting the interior color does not work from a user
> function, regardless of whether the target is the function within which
the
> function is being called or an entirely different (even hard-coded, rather
> than passed or calculated) cell.
>
> Any ideas?
>
> Thanks,
>
> -Martin
>
>
>