Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell background color (interior color) setting not working
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." wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with setting Interior Color in VBA | Excel Programming | |||
Setting Background Color RGB (Always goes to closest index color) | Excel Programming | |||
Conditionally setting background color of a cell | Excel Discussion (Misc queries) | |||
Problem in setting Cell Interior Color filling. | Excel Programming | |||
Setting cell background color based on value | Excel Programming |