On Fri, 22 Oct 2010 18:43:15 -0700, Mike S wrote:

On 10/22/2010 6:25 PM, LordHog wrote:
On Oct 22, 2:44 pm, 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

Will either of these approaches work?

http://office.microsoft.com/en-us/ex...001136627.aspx

Range("A1:A6").Interior.Color = RGB(200,160,35)

http://www.techonthenet.com/excel/ma...ange_color.php

LColorCells = "A" & LRow & ":" & "K" & LRow
Range(LColorCells).Interior.ColorIndex = 35

Sub Update_Row_Colors()
Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 7
LRow = 7
'Update row colors for the first 2000 rows
While LRow < 2000
LCell = "C" & LRow
'Color will changed in columns A to K
LColorCells = "A" & LRow & ":" & "K" & LRow
Select Case Left(Range(LCell).Value, 6)
'Set row color to light blue
Case "007007"
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid
'Set row color to light green
Case "030087"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid
'Set row color to light yellow
Case "063599"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid
'Default all other rows to no color
Case Else
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = xlNone
End Select
LRow = LRow + 1
Wend
Range("A1").Select
End Sub

http://www.mvps.org/dmcritchie/excel/colors.htm

Color Palette and the 56 Excel ColorIndex Colors

Thanks for that link. It has the HTML 'values'
declared as well. The one I found on the MS site did not.

I will maybe pool (pull!) some data together and make an even better
one! The one I linked has the names! This one, the HTML tags.

Maybe the spreadsheet should have some functions similar to that which
he desires, and then can be also used as a template for such. Though I
would have to store any macro code in a sheet as text for later inclusion
Otherwise, the template site will not accept it.

So I could make cells that perform it by a lookup, if possible, and I
could perfect his function or create others that do similar things to
make it a set of versatile color mod functions.