Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
I would like to write a function that will color the interior of a cell
according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
Hi
a function invoked from a worksheet cell can ONLY return values but is not allowed to change the Excel environment (e.g. formats or other cells). So no chance to achieve this with a formula entered in a cell. -- Regards Frank Kabel Frankfurt, Germany Avner Mediouni wrote: I would like to write a function that will color the interior of a cell according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
Avner,
A function called from a worksheet cell cannot change anything in the Excel environment, including the colors of cells. All it can do is return a value. Your code doesn't work because it attempts to change something that is off limits to code called from a cell. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Avner Mediouni" wrote in message ... I would like to write a function that will color the interior of a cell according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
You should also realise that even when you set a cell to an RGB value, Excel
will match this to the nearest Colorindex, and use that. So it may not be exactly the colour you wanted. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chip Pearson" wrote in message ... Avner, A function called from a worksheet cell cannot change anything in the Excel environment, including the colors of cells. All it can do is return a value. Your code doesn't work because it attempts to change something that is off limits to code called from a cell. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Avner Mediouni" wrote in message ... I would like to write a function that will color the interior of a cell according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
Hi Avner ,
As already stated by Chip you can do that in a Function, but Chip has a page on explaining the difference(s) between a Function and a SUB. "Macros as Opposed to Functions" as seen in his index http://www.cpearson.com/excel/topic.htm but the actual page is Macros And Functions http://www.cpearson.com/excel/differen.htm I have a page on color, and as previously stated by Bob Phillips you will be wanting to us Color Index values instead of RGB. Color Palette and the 56 Excel ColorIndex Colors http://www.mvps.org/dmcritchie/excel/colors.htm Somethings can be done in Conditional Formatting http://www.mvps.org/dmcritchie/excel/condfmt.htm and where Conditional Formatting can't be used because of the limitation of 3 colors (conditions) you can use an Event Macro. http://www.mvps.org/dmcritchie/excel/event.htm#case From what you supplied, perhaps you want something like: Sub ColorMe_37() ActiveCell.Interior.ColorIndex = 37 End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Avner Mediouni" wrote in message ... I would like to write a function that will color the interior of a cell according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
color function
You have the low down as to why a udf cannot directly
apply formats etc. However I find it is possible for a udf to trigger a normal macro (to do the formatting) by using SendKeys to send an already created keyboard shortcut to the macro. You also need some global variables to store the arguments of the function for use by the macro. Also as explained by others, an applied format will be one of the existing 56 palette colours, the nearest matched. If you want to apply your own colour, customize a colorindex with the macro, then apply same colorindex as the format. Add an extra argument for cIndex: Activeworkbook.colors(cIndex) = value The udf / sendkeys / macro method is far from satisfactory for general use. At the very least may need other things to avoid problems, depending on the context. However I did develop an instant palette customizer based on this approach that seems to work. Regards, Peter -----Original Message----- I would like to write a function that will color the interior of a cell according to RGB values. This is my code: Function InteriorColor(ByVal R as Integer, ByVal G as Integer, ByVal B as Integer) ActiveCell.Interior.Color = RGB(R, G, B) End Function This simple code refuse to work when I call it from a worksheet, although it works well when activates him from another subroutine. What is wrong ?!! Thanks for the help Avner R&D Physicist email: . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add fill color or font color to if function | Excel Worksheet Functions | |||
Color formula function | Excel Worksheet Functions | |||
IF function for cell color | Excel Worksheet Functions | |||
How to put color on if function | Excel Worksheet Functions | |||
cell color within a function | Excel Programming |