color function

[Avner Mediouni]

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: (e-mail address removed)

 

[Frank Kabel]

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.

 

[Chip Pearson]

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

 

[Bob Phillips]

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)

 

[David McRitchie]

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

 

[Peter T]

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