On Fri, 22 Oct 2010 18:25:41 -0700 (PDT), LordHog
wrote:

On Oct 22, 2:44*pm, LordHog wrote:
Hello,

* Note: I asked this question, originally, in the general group, but
advised to move the question to this group. Sorry for the cross-
posting.

* I am trying to write a macro/function in Excel 2007 that will set a
cell's color based upon RGB values passed in as arguments. *So the
function declaration will look like:

Public Function SetCellColor(HEX2DEC(A1), HEX2DEC(A2), HEX2DEC(A3) )

* ActiveCell.Cell.Interior.Color = RGB(100, 100, 100)

End Function

Then for a particular cell, say C12, I would set the cell to

=SetCellColor( HEX2DEC(C1), HEX2DEC(C2), HEX2DEC(C3) )

Obviously, this doesn't work as the ActiveCell set's the active cell's
color and not the cell that has the =SetCellColor set for it. *Also, I
get an error 1004 when I run this code.

* Does anyone know how I might set accomplish this task. *Oddly, if I
use the following instead, it will actually set the active cell's
border without throwing an error.

ActiveCell.Cells.Borders.Color = RGB(r, g, b)

I have no idea why this one works, but not the other one. Any help is
greatly appreciated.

Mark

Hiya James,

Thanks for the information regarding a function can't change a cells
color. This is rather disappointing as it make any solution less
dynamic. It seems I will need to call this function when ever the
sheet changes then either hard code the ranges in or places the ranges
in a cell then update those. A little duct tape like, but hopefully I
am able to get it working. Thanks.

Mark


Array them all out, and perform a vlookup to paste that color in using
the rgb numbers as the lookup criteria. So the function would always
call data from the array. (do not know if this would work) (I am not in
any way knowledgeable here)

Can we make 255 validation rules? That would work if possible, no?