Cell background color (interior color) setting not working

[Martin E.]

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

 

[Tom Ogilvy]

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.