Cell background color (interior color) setting not working

M

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top