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 |
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 |
All times are GMT +1. The time now is 08:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com