Setting a cell's color based upon RGB values

[LordHog]

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

 

[James Ravenswood]

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

Hi Mark:

Functions return values and cannot modify cell colors. You need a
macro. Say A1 thru A4 contain:
B9
120
230
40

These values are a cell address and the red, green, and blue
integers. The following macro:

Sub ColorMeElmo()
Dim addy As String, red As Integer, green As Integer, blue As Integer
Dim r As Range
addy = Range("A1").Value
red = Range("A2").Value
green = Range("A3").Value
blue = Range("A4").Value
Set r = Range(addy)
clr = RGB(red, green, blue)
r.Interior.Color = clr
End Sub

will apply the color to the cell.

 

[LordHog]

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

 

[Mike S]

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/excel-help/change-cell-background-color-using-vba-HA001136627.aspx

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

http://www.techonthenet.com/excel/macros/change_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

 

[CellShocked]

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

Take a look at this template on the MS community site:

http://office.microsoft.com/en-us/templates/CT010253058.aspx?tl=3#pg:2|ai:TC030000424|


or

http://preview.tinyurl.com/33c7y4r

There, you can get all the values AND the names for them for the "Web"
set of colors.

 

[CellShocked]

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?

 

[CellShocked]

Will either of these approaches work?

http://office.microsoft.com/en-us/excel-help/change-cell-background-color-using-vba-HA001136627.aspx

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

http://www.techonthenet.com/excel/macros/change_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.