Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
On Oct 22, 5: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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
On Fri, 22 Oct 2010 14:44:18 -0700 (PDT), 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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell's color based upon RGB values
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to populate data in a cell based on another cell's color | Excel Discussion (Misc queries) | |||
UserForm - Setting focus to a CommandButton based on a Cell's contents | Excel Programming | |||
Have row color change to one of 8 different colors based on one cell's value (I, O, C, T, L, E, X, A) | Excel Discussion (Misc queries) | |||
Change a cell's color based on return value of a formula | Excel Programming | |||
Setting cell background color based on value | Excel Programming |